Contextures News

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.

Fill the Dates

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.

Symbols in Drop Down List

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.

Excel Training

• Free Webinars: Mynda Treacy is offering two free one-hour webinars -- 1) How to Build Excel Dashboards, and 2) Dashboards with Power Query and Power Pivot. Get the details, and sign up for a date and time that is convenient for you.

Excel Articles

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.

Winter Is Back

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:

https://www.contextures.com/newsletter/excelnews2016/20160216ctx.html

_____________

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.