Convert number to date

[fearless2002uk]

I have a spreadsheet with a column of 3500 numbers (in this format 20030129 or 19971112 for example) they are infact dates - I am struggling to format them as such. HELP!

Many thanks in advance

 

[3xcelDummy]

OR,

Try the following formula:

=DATE(RIGHT(A1,4),1*(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,255)),LEFT(A1,SEARCH(".",A1)-1))


23.11.2015 will be converted to date format 23/11/2015

This worked too, although it did not convert the periods to slash, but still converted it to dates so i can change the date formatting to dd/mm/yyyy. Thank you!

 

[jeremymayfield]

I need help, i have read this whol thread and got nothing,

I have date and time like this stored in SAP, moved to Excel,

9212015050630

<colgroup><col width="158"></colgroup><tbody>
<!--StartFragment-->
</tbody>

that is date and time with the seconds. I need it in two fields, first 6 are date, and will vary based on month, obviously the first zero is missing thank you excel. but it should be 09212015050630, but its not. I need the Date to appear like 9/21/2015 and the time as 05:06:30 so that we can manipulate it. Any ideas, i have tried the formulas in the thread and i can not get there. Currently i am using this, =DATE(MID(B2,4,4),MID(B2,2,2),MID(B2,1,1)) and its giving me the wrong year, says my year is 2016 not 15, and day is 9 vs 21, but the month is correct.

HELP

 

[Rick Rothstein]

Use this formula for the Date (you will have to use Cell Formatting to make it look like a date)...

=0+TEXT(LEFT(B2,LEN(B2)-6),"00\/00\/0000")

and this formula for the Time (you will have to use Cell Formatting to make it look like a time)...

=0+TEXT(RIGHT(B2,6),"00\:00\:00")

 

[Arie Bos]

Elaborating on this, I have two cells with a date:
A1 contains =TODAY(), A2 gets a date from another workbook using Index-Match function. I want to know the number of months between the two dates, using the DATEDIF function. result is a #NUM error.
so, I put =VALUE(A1) in B1, and =(VALUE(A2) in B2, but result is the same. THen I tried using =TEXT(B1;"dd-mm-yyyy") in C1 (same for B2 in C2), but I keep getting the #NUM error.