Fixing Date Formatting.

[Actionhank1786]

Hi everyone,
I apologize if this is a constantly covered topic, but I've searched Google high and low, and haven't been able to find anything that could successfully help me out.
I've tried myself and things I've found on Google, but so far with no luck.
The Company i work for has asked me to create an excel spreadsheet that's just a pretty simple document to send to our clients.
I've got the rows and columns for Names and Comments without too much problem, but while trying to set up the Date of Birth and Date of Service columns i'm running into a wall.
The place i work for enters dates into our medical program as, for example:041786 to get it to show up as 04/17/1986 in the program.
I was hoping to get excel to do the same thing, but for some odd reason it seems to be figuring out the dates in a different way. When i type 041786 into it comes up at 05/27/14. I'm not sure how it's getting to this, or if it's taking the current date into account for some reason. I was hoping to format the column for the DOS in the same way.
I've got a formula for the patient's age which seems to be working alright, so now my primary issue is figuring out what Excel magic is changing the date on me.

tl;dr: Trying to make the date entered as 010204 come up at 01/02/2004, but i'm awful with Excel.

Thanks in advance for any advice, everyone.

[tigeravatar]

Actionhank1786,

Welcome to the forum!
Attached is an example workbook based on the criteria you described.
Row 1 is a header row so actual data starts in row 2
Column A contains Names
Column B contains the date entry in the format that you will be entering it (041786, 010204, etc)
Column C contains the date entry converted to the desired date.

In order to get the leading 0 to stay, column B has had a custom format applied to it of "000000" (no quotes)
In order for the date to show as desired after conversion, column C has had a custom format applied to it of "mm/dd/yyyy" (no quotes)
In cell C2 and copied down is this formula to perform the date conversion:

Please Login or Register  to view this content.

Does that work for you?

[Actionhank1786]

I will give this a shot, and see what happens.
Thanks for the quick reply! You may have just saved my desk from a violent collision with my face.

:edit:
I was curious, is there a way to get this to appear in just one column?
They're wanting a lot crammed into one page, so i've landscaped it, but having a section for DOB, and then the converted DOB would take up an extra column.
Would i just be able to edit the formula to change whatever i type into the column from 041786 into 04/17/86?

[tigeravatar]

You could use the Text to columns feature to convert the entries to MDY dates, but you'd need to change the format of the column afterwards to the mm/dd/yyyy format, and repeating the process if you added or changed dates later would be a hassle. Other than that you would need VBA to convert the entries into the desired dates within the same cell on the fly.

Easier than either of those however, would be to simply hide the Date Entry column(s) whenever you're presenting the data as a report or printing it out.

[Actionhank1786]

You could use the Text to columns feature to convert the entries to MDY dates, but you'd need to change the format of the column afterwards to the mm/dd/yyyy format, and repeating the process if you added or changed dates later would be a hassle. Other than that you would need VBA to convert the entries into the desired dates within the same cell on the fly.

Easier than either of those however, would be to simply hide the Date Entry column(s) whenever you're presenting the data as a report or printing it out.

Ahh, so, essentially the dates would have to be entered first, then converted?
I guess i'll just tell them they will have to type the / in the date. It won't be a big deal for anyone, i just figured it might be a little quicker if they could type the numbers straight in and not have to take those extra key strokes to hit the forward slash. It won't be a big deal though.
Thanks for all the help! I'll definitely have to save that spreadsheet for future reference.