Microsoft Excel: Excelling at The Dating Game

Today's blog is by trainer and guest blogger, Phil Wiest, PMP®  PMI-ACP® PMI-PBA® Microsoft Office Master®. Phil is a sought-after trainer on Excel topics because he makes it so simple that even a novice can become proficient very quickly. You can see more about Phil here on his LinkedIn page. 

=========================================  

Not long ago, I overheard Dave at the water cooler.

“I can’t get a date for Valentine’s Day!”

“What?” I asked.

“Well it’s not me, it’s Excel. I can’t get a date for Valentine’s Day in Excel!    

 

 

Excel and Dates     

Have you ever opened an Excel workbook with messed up data?

Who hasn’t?

In this case it’s a matter of system generated dates – like “20190214” (to some people, Valentine’s Day)

When there’s a date in Excel that’s not recognized as a date, you’ve got a dating problem.

Excel doesn’t have trouble doing calculations with “slash-dates” or “dash-dates,” but when there’s a date that doesn’t look like a date to Excel, what do you do? (Excel thinks you have 20,146,716 days to go!)

Fake dates don’t work when doing calculations.

When you’re needing to calculate important things like:

• The number of days until a deadline
• The number of years until retirement
• The number of years you’ve been dating

You need to convert fake dates to real dates using today’s Text-To-Columns Trick

The Secret Text to Columns (for Dates) Hack     

The key to this hack is that the feature is titled, “Text to Columns” and you’re using it for numbers, not text! In this example, “20190214” is the pattern that means February 14, 2019, but was created as a string of numbers.   

 

 

Let’s say you’ve received an Excel workbook that’s loaded with dates that aren’t interpreted by Excel as dates.

 

SkillSteps: To convert the dates: Highlight the Fake Dates > Select Data > Data Tools > Text To Columns

Once you’ve opened the dialog, there are three steps to complete.

SkillSteps: In Step 1, confirm that Delimited is selected and click, Next.

SkillSteps: In Step 2, When you’re asked to identify the delimiter(s) > uncheck all the sample delimiters and click Next.

 

SkillSteps: In Step 3,  ensure Excel knows the date pattern is Year Month Day. Select Date > Select YMD (1) > Select the Destination Cell (2) > Finish (3)

Once you’ve clicked Finish… the transformation of those numbers to real dates is complete:

It’s time to celebrate. You’re finished…and you have plenty of time to go out on a real date… loving the way Excel and Text to Columns saved the day!

Because your date calculations finally work…

And all that’s left is to wish Dave and Text To Columns many happy years together!