Number to date format

[swamymg]

Hi,
In Cell A1 i have 50884 : its in text format. i need 8th may 1984 in cell b1 using formula.

for time also: time is text format. eg: 0120 , i ned it has 1:20.

pls tell me the formulas.

Thank you.

[FDibbins]

That number represents 4/24/2039 not 8 may 1984 - that would be 41402

Anyway, 1st, convert the text to a number = A1*1
then format date as required

[swamymg]

=datevalue(text(a1,"00-00-00"))

[FDibbins]

Did you try my suggestion?

[AlKey]

That number represents 4/24/2039 not 8 may 1984 - that would be 41402

Anyway, 1st, convert the text to a number = A1*1
then format date as required

Would it be 30810 for 5/8/1984 ?

[AlKey]

Maybe this?

=TEXT(A1,"mm-dd-yyyy")&" "&TEXT(A1,"h:mm")

[FDibbins]

Yes you are correct, 30810 , I forgot to add/change the year. Thanks for the catch

[martindwilson]

50884 can be 8 may 84 or 5 aug 1984 now it will depend on your regional settings how it comes out
in mine
=DATEVALUE(TEXT(A1,"00\/00\/00")) comes out as 5 aug 1984
so you may need
=DATE(RIGHT(TEXT(A3,"000000"),2)+IF(RIGHT(A3,2)+0<50,2000,1900),LEFT(TEXT(A3,"000000"),2),MID(TEXT(A3,"000000"),3,2))
to get 8 may 84 for dates between 1950-2050

[AlKey]

Yea here it is:

=IF(LEN(A1)=5,LEFT(A1,1)&"-"&MID(A1,2,2)&"-"&RIGHT(A1,2),LEFT(A1,2)&"-"&MID(A1,3,2)&"-"&RIGHT(A1,2))

or this:

=DATEVALUE(IF(LEN(A1)=5,LEFT(A1,1)&"-"&MID(A1,2,2)&"-"&RIGHT(A1,2),LEFT(A1,2)&"-"&MID(A1,3,2)&"-"&RIGHT(A1,2)))

[swamymg]

That number represents 4/24/2039 not 8 may 1984 - that would be 41402

Anyway, 1st, convert the text to a number = A1*1
then format date as required

=a1*1 is ok but formating is not happening.
i used one more function =datevalue(text(a1,"00-00-00")) it works.

thank you.

[swamymg]

if i format its not showing the desired result.

do u have something for time?

thanks

[AlKey]

What format do you need?

[swamymg]

0120 its in text format. i need it in time format. it should be HH:MM, ie., 1:20

thank you.

[AlKey]

I would assume that time in a different cell.

=TIMEVALUE(TEXT(LEFT(A1,2)&":"&RIGHT(A1,2),"h:mm"))

[swamymg]

I would assume that time in a different cell.

=TIMEVALUE(TEXT(LEFT(A1,2)&":"&RIGHT(A1,2),"h:mm"))

its working fine. Thank u

[AlKey]

You're welcome. Don't forget to thank those who helped by clicking on Add Reputation * and please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).