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.

 

[kumara_faith]

Hi Rich Rothstein,

Thank you for your solution. I am getting #value! when I use this formula. Here is a screen print if this helps.

 

[Tom.Jones]

In A1 You have to put this: 770205145598

 

[kumara_faith]

Hi,

I am copying the data from another sheet which is in an excel table. The format in the original sheet is number. The result is as per the screen print.

 

[Alex Blakenburg]

It looks to me that your default date format (Region Setting) is dd/mm/yyyy.
Yes you can reformat it for display purposes to show mm/dd/yyyy but any date conversion is still going to use your Region setting.
Based on this Rick's formula won't work for you, it is based on an US format date setting. It is giving #Value because it is using the 24 in your value in the mm position in your date format dd/mm/yyyy
If you want Excel to determine whether to use 19 or 20 for the year, then you simply modify @richh's formula dropping the "19" prefix.

=DATE( LEFT(A1,2), MID(A1,3,2), MID(A1, 5,2))

 

[johnnyL]

@kumara_faith the problem you have is, you have the cell in the B column formatted as a date. Please see Post #5 in this thread.

 

[johnnyL]

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

@Rick Rothstein If I may rephrase, Your formula, doesn't refer to 'date' anywhere in it, so where is the magic in your formula that it knows that the 2 digits should be a year?

 

[Rick Rothstein]

I assumed from the OP's thread title that dates were in the same format as on my system, namely, mm/dd/yyyy. I took the first six characters of the cell value (which the OP said describes a date) and put them into that format (using only two digits for the year), so the text manipulation part of my formula produced this... 02/05/77. Now that is just a text string; however, in Excel, if you involve a text string number in a mathematical calculation, Excel will convert the text to the actual number it represents in order to perform the mathematical operation. A date is just a number to Excel, so I wrapped the text concatenation in parentheses to group them all together, then I added zero to that (adding zero to any number does not change that number) so that Excel would convert it from text that looked like a date to a real date value. However, based on Alex's comment in Message #14, apparently the OP's native date format in not mm/dd/yy but rather dd/mm/yy and that he only wants the date shown as mm/dd/yy. Because of that, the text date I concatenated together appears to have the month and day values in the wrong order for the OP's system which is why it did not work for him.

 

[johnnyL]

Ahhhhh. If I wasn't surrounded by all of these trees, I possibly could see the forest occasionally. Thank you @Rick Rothstein for the explanation.

 

[kumara_faith]

Hi,

My apologies. I have tried all three formula suggested but still the same. My apologies again for wasting your time, but could you guide me what actually I would need to do to get it right ?

 

[Rick Rothstein]

What is the natural date format on your computer? That is, when you put a real date in an Excel cell, does it display as mm/dd/yyyy or as dd/mm/yyyy?