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

 

[Rick Rothstein]

Please guys, i have successfully converted from PDF to Excel but the dates are scattered. I need to convert 24112110 into something like this 24/12/10. The major issue is that the slash has been converted into 1 and i have a whole lot of them

Describe "dates are scattered" for us...

1) Are they scattered about within one column (if yes, which column and what else is in the column)?

2) If multiple columns, can you tell us which ones?

3) After converting the values, what is the resulting value suppose to be? In other words, is 24/12/10 a date of some kind?

4) Are your numbers always 8 digits long?

 

[ritabee]

Hello!

Thank you for this solution! I have a couple of follow up questions. My data goes back to the 1800's and when I use the =TEXT(A1,"0000-00-00")+0 solution it only works on dates after the 1900's. Otherwise the solution returned is #VALUE. If I remove the +0 then it will return the text string, but will not recognize it as a date.

Furthermore, I have some date values with a time stamp. For example the value 197307121000 would mean 1973-07-12 (YYYY-MM-DD) at 10:00AM. I tried to use the text solution above with =TEXT(A1,"0000-00-00 00:00")+0 but it did not work.

Any ideas would be truly appreciated.

Thank you!!!!!

 

[Rick Rothstein]

Thank you for this solution! I have a couple of follow up questions. My data goes back to the 1800's and when I use the =TEXT(A1,"0000-00-00")+0 solution it only works on dates after the 1900's. Otherwise the solution returned is #VALUE. If I remove the +0 then it will return the text string, but will not recognize it as a date.

Furthermore, I have some date values with a time stamp. For example the value 197307121000 would mean 1973-07-12 (YYYY-MM-DD) at 10:00AM. I tried to use the text solution above with =TEXT(A1,"0000-00-00 00:00")+0 but it did not work.

Excel does not do dates before 1900 although I think there may be some kinds of workaround out there (try doing a Google search to see). As for your second question, try this formula...

=TEXT(LEFT(A1,8),"0000-00-00")

 

[ritabee]

Thanks!

I am trying to make the early dates work with this add in I found: Extended Date Functions. Seems promising.

Thanks for your suggestion on the first part!

Cheers,
Ria

Excel does not do dates before 1900 although I think there may be some kinds of workaround out there (try doing a Google search to see). As for your second question, try this formula...

=TEXT(LEFT(A1,8),"0000-00-00")

 

[3xcelDummy]

my dates are formatted as

23.11.2015

<tbody>
</tbody>

i tried formatting it to dates, no luck.. then text then used this =--TEXT(A1,"0000-00-00") and =TEXT(A1,"0000-00-00")+0 but i am receiving a #VALUE! error, i need to convert about 1000 rows of these and i don't want to do it manually... help!!!

 

[FormR]

my dates are formatted as

23.11.2015

<tbody>
</tbody>

You could try:
=SUBSTITUTE(A1,".","/")+0

 

[3xcelDummy]

You could try:
=SUBSTITUTE(A1,".","/")+0

- Hi this did not work for me, so what I did was tried Find/replace, it worked on some but others didn't, why is that? Sample column below, i was able to replace "." with "/", format the cells by date, but not all cells followed the format.

Start Date

23/11/2015

29/12/2014

December 01, 2015

April 05, 2015

September 03, 2015

February 03, 2015

17/08/2015

16/03/2015

16/03/2015

27/04/2015

24/08/2015

December 01, 2015

14/09/2015

July 09, 2015

29/06/2015

20/07/2015

January 06, 2015

<colgroup><col></colgroup><tbody>
</tbody>

---

 

[FormR]

Looks like your system is expecting dates in the format of MM/DD/YYYY - you could try this instead:

=SUBSTITUTE(REPLACE(MID(A1,4,7),3,0,"."&LEFT(A1,2)),".","/")+0

 

[cbatrody]

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

 

[3xcelDummy]

Looks like your system is expecting dates in the format of MM/DD/YYYY - you could try this instead:

=SUBSTITUTE(REPLACE(MID(A1,4,7),3,0,"."&LEFT(A1,2)),".","/")+0

This worked Perfectly! Thanks!!!