Change date to number

[Guest]

Client has dates with custom format yyyymmdd. She needs the date to show in
that format but not be a date anymore. For example, the custom format
displays the date as 20061006 but she needs this value to display exactly
like this but no longer be a date. Everything I've tried keeps reading the
value as the serial number rather than literally what is displayed. I've
tried the text function, left and mid and right, and text to columns. Any
other suggestions??

 

[Ron Rosenfeld]

Client has dates with custom format yyyymmdd. She needs the date to show in
that format but not be a date anymore. For example, the custom format
displays the date as 20061006 but she needs this value to display exactly
like this but no longer be a date. Everything I've tried keeps reading the
value as the serial number rather than literally what is displayed. I've
tried the text function, left and mid and right, and text to columns. Any
other suggestions??

Since the contents is an actual date, perhaps this:

=YEAR(A1)*10^4+MONTH(A1)*100+DAY(A1)


--ron

 

[vezerid]

maryj,
the following formula will produce, in a separate cell, the same
display as in A2, only it is now text:
=TEXT(A2,"yyyymmdd")

If you want to use a column for further data entry, in which your entry
will not be interpreted as a date, format the cells as Text
(Format|Cells...|Number tab)

HTH
Kostis Vezerides

 

[Guest]

Try this
=CONCATENATE(YEAR(A1),TEXT(MONTH(A1),"00"),TEXT(DAY(A1),"00"))
It will convert the date into text and keep the same format
Alok

 

[Guest]

The TEXT function should work. Did you try it like this:

=TEXT(A1,"yyyymmdd")

HTH,
Elkar

 

[Guest]

Thank you!!! Forgot about setting the format in the text function like that!