Converting Dates in General Format to Dates Data Acceptable to Excel

Hello all,

Today I was given a task to find the age of a list of persons given their date of birth. Simple task right? Wrong!
I figured I could simply use what fontstuff described here and get it easily but it seems my data was not formatted. So first step was to convert it from General to Date? Wrong!

After doing some research I realized the date data wasn’t detected by Excel and it kept giving me #VALUE errors. My next step was to convert it somehow to the acceptable format. After some more googling, I found a post by Aladin Akyurek. He described that you first determine if the date is True or False. To do this you issue the command:

=ISNUMBER(A1)

If it results in a False, you know your date data is not being recognized. You can follow what he did in his post by copying an empty cell, selecting the range of cells, then paste special then “add” and format the range as date. But this did not work for me. I had to use another tip from yuniar to simply convert the data to Date Format using “Data->;Text to Columns” and Column Data Format must be set to Date and the specified format.

Doing this worked excellently! My data read as True now as well based on the command above.

Now, back to the original tutorial by fontstuff, when i set a date as today, I made sure the format matched that of the parsed data (for me it was dd/mm/yyyy) however, when i simply minus the cells and divided by 365 (to get the age in years) i got another date! To fix this you need to view the serial number behind the date. I found this simple way to convert it in both directions from this post. For my case I had to simply hit “Ctrl+Shift+~” and I got my age.

Side Tip: As you will realize the data will always keep up to date as the “today” date will change. Now, to get things looking neatly for instance no decimal places and their actual age not something rounded off once it has a decimal greater than 0.5, you can use the RoundDown function.

The only real issue I came across is that dates now have to be entered in month-day-year style if it’s a month/day that is less than 12.

I’d like to thank all of those authors on the sites where I got my information from and thank you for reading!

God Bless

Regards.

(and yes I just had to put that rage face)

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: