# Extract date in mm/dd/yyy

#### kumara_faith

##### Well-known Member
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

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
=DATE("19" & LEFT(K11,2), MID(K11,3,2), MID(K11, 5,2))

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

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.

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

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

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))

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

Last edited:
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:
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.

Replies
13
Views
596
Replies
7
Views
467
Replies
7
Views
502
Replies
4
Views
337
Replies
4
Views
222

1,196,507
Messages
6,015,604
Members
441,905
Latest member
Jean207

### 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.

### Which adblocker are you using?

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

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