Extract date in mm/dd/yyy

kumara_faith

Well-known Member
Joined
Aug 19, 2006
Messages
918
Office Version
  1. 365
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.
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
=DATE("19" & LEFT(K11,2), MID(K11,3,2), MID(K11, 5,2))

Change K11 to whatever cell your emp num is stored in.
 
Upvote 0
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.
 
Upvote 0
You can format the cells to show a different date via the format selector (Alt H - F-M)
 
Upvote 0
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))
 
Upvote 0
I'll tell you how, your formula you submitted converts the two digit years of 00-29 as 20xx, for what reason? Beats me.
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,752
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top