ExcelBanter - convert numbers

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - convert numbers (https://www.excelbanter.com/excel-discussion-misc-queries/165128-convert-numbers.html)

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

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
--
Kevin Backmann

"Chi" wrote:

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

datatext 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

"Chi" wrote in message
...
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

To make a date:

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

--
Gary''s Student - gsnu200754

"Chi" wrote:

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

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

"Chi" wrote:

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

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" wrote:

datatext 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

"Chi" wrote in message
...
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

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

"Chi" wrote in message
...
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" wrote:

datatext 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

"Chi" wrote in message
...
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

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.
--
David Biddulph

"Chi" wrote in message
...
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" wrote:

datatext 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

"Chi" wrote in message
...
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

Hi Teethless,

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

"Teethless mama" wrote:

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

"Chi" wrote:

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