Split Date and Time - No Formulas
Feb 16, 2016
See how to split dates and times, build hyperlinks, and more, in this week's Excel news. Which article was most helpful to you this week?
- Debra - ddalgleish @ contextures.com
Visit my Excel website for many more tutorials and videos.
Did you try last week's tip on how to use the INT function? You can get
the date or the time from a cell that had a combined date and time:
=INT(A2) for the date and =A2-INT(A2) for the time.
This week, we'll try something even easier. If you're using Excel 2013 or later, you can use the Flash Fill featue to get the date or time in a separate column, without formulas. Perhaps you've used Flash Fill to split names into separate columns. Here's how you can use Flash Fill for dates or times.
Type the first two dates in column B, based on the dates in column A. Then, go to the Data tab on the Excel Ribbon, and click Flash Fill. The rest of the dates should fill in, and you can use the Flash Fill options box to confirm or undo the changes. You can do the same thing to fill in the Time column.
Drop down lists on a worksheet make it easy to enter data. For a bit of variety, you can use symbols, instead of words. I used heart symbols in my Excel Valentine card, and you could use happy faces, or other symbols in your worksheet. For example, let people rate a new feature by selecting from one to five happy faces.
To type a heart symbol, press the Alt key, then tap the 3 on the number keypad (not the numbers at the top of the keyboard). Or, since St. Patrick's Day is only a month away, use Alt + 5 (number keypad), to insert shamrock symbols in all your workbooks.
Here are a couple of Excel articles I read recently, that you might find useful.
Financial Models -- On the Sophisticated Finance blog, Robert Harker has collected the links to his articles on Excel and Financial Models. The articles were written a few years ago, but the information is timeless. (Level - Intermediate)
Build Hyperlinks -- To make it easy for people to find files, Ben J. Kusmin shows how to create a list of hyperlinks on a worksheet. The file path information is entered in one cell, and a formula combines that with the file names. (Level - All)
More Excel Articles -- Find links to more articles in the latest Excel Weekly Roundup on my Contextures blog -- you'll see an animated chart with a dinosaur chasing a human.
And for a bit of humour, read the weekly collection of Excel tweets.
After a long stretch of unseasonably warm weather, winter has come roaring back. We've had snow and bitter cold temperatures, and Canada feels like the True North once again.
I checked the back yard after a recent snowfall, and everything was covered with a blanket of fluffy snow. This window box caught my eye - a little spot of colour in a sea of white. I should have cleared out those dead flowers in the fall, and they are a sad contrast to the cheerful painted flower on the box. I hope you're enjoying whatever season you're in, and that the temperatures aren't too extreme -- hot or cold.
NOTE: If you have any problems with the links in the email, you can see this newsletter on my website -- copy this link and paste it into your browser:
See more recommended Excel products.
Note: I am an affiliate for some of the products mentioned in this newsletter, and earn a commission on the sales.
Last updated: June 14, 2017 11:45 PM