Extract date in mm/dd/yyy

kumara_faith

Well-known Member
Joined
Aug 19, 2006
Messages
651
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

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

richh

Board Regular
Joined
Jun 24, 2007
Messages
243
Office Version
  1. 365
  2. 2016
=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

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

Board Regular
Joined
Jun 24, 2007
Messages
243
Office Version
  1. 365
  2. 2016
You can format the cells to show a different date via the format selector (Alt H - F-M)
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,360
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

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

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,369
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Nov 7, 2011
Messages
1,360
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

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

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,360
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
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:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,369
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,141,818
Messages
5,708,769
Members
421,589
Latest member
b_gernert

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
Top