Date converted to Text Number

[Guest]

If a cell is converted from a date format to text format, the date in that
cell is converted to a 5-digit number.

I would like to know how excel calculates that number so that I can reverse
that formula to figure out what dates I originally had in a bunch of cells
which now have 5-digit numbers in them, without having to change each cell
back to date format.

thanks,

m-

 

[VBA Noob]

Dates in excel start from

01/01/1900 =1

so 28/7/06 = 38926

VBA Noo

 

[Gord Dibben]

The 5 digit number is the number of days since Jan-00-1900

Jan 1st, 1900 is day 1

For more on this subject see Chip Pearson's site.

http://www.cpearson.com/excel/datetime.htm#AddingDates

Why not just re-format to Date?


Gord Dibben MS Excel MVP

 

[Guest]

Thanks!

 

[Guest]

Thank you! very interesting.

The 5 digit number is the number of days since Jan-00-1900

Jan 1st, 1900 is day 1

For more on this subject see Chip Pearson's site.

http://www.cpearson.com/excel/datetime.htm#AddingDates

Why not just re-format to Date?


Gord Dibben MS Excel MVP

 

[Guest]

To answer your question:

all the cells in this particular column, i have decided, need to be in text
format. only about 10% of the cells had a date only in them (the ones with
date & text converted fine). So, I would still have to convert them to date,
then convert back to text again, and type the text date into the text
formatted cell.

my conclusion:
too much work. the dates really aren't that important anyway....
but I'm glad I learned something today!

Thanks again.

m-
, - . _ , - .
\ /, " ,\ /
{ o }
Ŭ

 

[Gord Dibben]

Thanks for the feedback.

Chip has amassed a wealth of information on his site.

When you get time, browse around.


Gord

To answer your question:

all the cells in this particular column, i have decided, need to be in text
format. only about 10% of the cells had a date only in them (the ones with
date & text converted fine). So, I would still have to convert them to date,
then convert back to text again, and type the text date into the text
formatted cell.

my conclusion:
too much work. the dates really aren't that important anyway....
but I'm glad I learned something today!

Thanks again.

m-
, - . _ , - .
\ /, " ,\ /
{ o }
?

Gord Dibben MS Excel MVP