convert numbers

[Guest]

Hi,

Would you please show me how to convert numbers to age or birthday date?

Ex: I would like to convert 5181965 to 05/18/1965 or 42 (age)
Thank you
Chi

 

[Guest]

Try out this formula from Chip Pearson's Web page. The formula assumes that
the date value is in cell A2:

=DATEDIF(A2,TODAY(),"y")&" years, "&DATEDIF(A2,TODAY(),"ym")&" months,
"&DATEDIF(A2,TODAY(),"md")&" days"

To get additional information regarding date math go the Chip's site at the
following URL:

http://www.cpearson.com/excel/datearith.htm

 

[Peo Sjoblom]

data>text to columns, click next twice then under column data format select
date and MDY and click finish, when the conversion is done use

=DATEDIF(A2,TODAY(),"y")

where A2 is the cell with the converted number


--


Regards,


Peo Sjoblom

 

[Guest]

To make a date:

=DATE(RIGHT(A1,4),LEFT(TEXT(A1,"00000000"),2),MID(TEXT(A1,"00000000"),3,2))

 

[Guest]

=DATEDIF(TEXT(A1,"00\/00\/0000")+0,TODAY(),"y")

 

[Guest]

Thank you for all responds!

Hi Peo,

It works!!! Thanks. However, in my excel sheet some numbers (cells) were not
converted. I think because the formula works with 8 digits, but not 7.

EX: 10221968 became 10/22/1968. It is perfect! However, the numbers,
9111966, wasn't converted. I tried to add 0 to it so it will become 09111966.
Excel deleted the 0 as soon as I added it in the cell. Would you show me how
to add 0 in all cells?


--------------------

Would you also show me how to convert the birthday date to age please?

Is there a way to make it works?

Thanks

 

[Peo Sjoblom]

If you precede the entry with an apostrophe ' so you always use 8 (or 6
digits if you use mmddyy)
it will work and it will return 09/11/1966

or use the formula

=TEXT(A1,"00\/00\/0000")

or to get a real numerical date

=--TEXT(A7,"00\/00\/0000")

and format the cell as mm/dd/yyyy


--


Regards,


Peo Sjoblom

 

[David Biddulph]

If you have a number of 7 digit cells, you can use the formula ="0"&A2 to
add the leading zero. Copy the results and use Edit/ Paste Special/ Values
to paste the value (text string) in place of the formula. You can then use
the Data/ Text to Columns on that 8 digit text string.

 

[Guest]

Hi Teethless,

Excellent!!! Thank you so much. I also thank to Kenvin B, Peo, David and
Gary!!
Chi