Extract date in mm/dd/yyy

[kumara_faith]

Hi,

I have a list of employee numbers in the following format:

770205145598

The first two numbers are the year, second two digits are the month and the next two digits are the day. So, in this case the employees' date of birth is February 5 1977. Is there a way to extract the date of birth in mm/dd/yyyy format ? Thank you in advance.

 

[richh]

=DATE("19" & LEFT(K11,2), MID(K11,3,2), MID(K11, 5,2))

Change K11 to whatever cell your emp num is stored in.

 

[kumara_faith]

Hi richh,

Thank you for your time and solution. When I used the formula, ( my data is in an Excel table), the formula first shows as numbers. Then when I convert to date, it shows as dd/mm/yyyy instead. Is there anything I am doing wrong? Appreciate your assistance. Thanks.

 

[richh]

You can format the cells to show a different date via the format selector (Alt H - F-M)

 

[johnnyL]

ADVERTISEMENT

format the cells to custom ---> mm/dd/yyyy

 

[Rick Rothstein]

We don't know your data but I would point out that richh's formula will only work for dates before the year 2000. Here is another formula that you can consider which will handle dates on both sides of the year 2000 the way Excel would do it (the cutoff between 1900 dates and 2000 dates being a setting in your Windows operating system--on my system that is two digit numbers before 20 being considered as 2000 dates)....

=0+(REPLACE(MID(A1,3,4),3,0,"/")&"/"&LEFT(A1,2))

 

[johnnyL]

ADVERTISEMENT

@Rick Rothstein how can your formula ever determine a given two digit year as 19xx or 20xx?

 

[johnnyL]

I'll tell you how, your formula you submitted converts the two digit years of 00-29 as 20xx, for what reason? Beats me.

 

[Rick Rothstein]

There is a Windows setting for when a 2-digit year is considered to be in the 1900s or 2000s... that has been set at a default of 2030 since Windows was created (this can be changed by someone with Windows administrator privileges though)... Excel uses that Windows setting for how it converts dates having 2-digit years. You can see this by entering 01/01/29 in one cell and 01/01/30 in another cell. The first one will have the year 2029 whereas the second one will have the year 1930. My formula makes use of this automatic year creation from a 2-digit specified year.

 

[Tom.Jones]

@Rick,