Date formatting

N

nick

Hi,

How do i convert a standard date in to yyyymmdd format? I have to use a
function to do this. I cannot just change the format of the cell. Can anyone
help me with this? Thanks
 
A

Ardus Petus

Why can't you change the format of the cell?

if you want to change yyyymmdd text into date data, use the following:
=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

HTH
 
D

Dave O

When you say "cannot just change the format of the cell", I'm guessing
you have some kind of requirement to deliver the data somewhere else
and need to show the actual numbers, instead of the Excel-readable
date. Is that correct?

Assuming you have an Excel-readabel date in cell A1, try this formula:
=YEAR(A1)&TEXT(MONTH(A1),"00")&TEXT(DAY(A1),"00")
 
D

Dav

You either format the cell with a custom format of yyyymmdd or apply a
formula to make it appear elsewhere such as =TEXT(A1,"yyyymmdd") but
this turns it into text, it is no longer numeric

Regards

Dav
 
C

CLR

You can convert an Excel DATE to a TEXT display with.......

=TEXT(A1,"yyyymmdd")

But the result is TEXT, no longer a DATE for Excel.

Vaya con Dios,
Chuck, CABGx3
 
G

Gary''s Student

An alternative:

=YEAR(A1)*10000+MONTH(A1)*100+DAY(A1)

and format the cell as General. So if A1 contains =TODAY(), then the
formula will return: 20060510
 
N

nick

Thank You all....

Gary''s Student said:
An alternative:

=YEAR(A1)*10000+MONTH(A1)*100+DAY(A1)

and format the cell as General. So if A1 contains =TODAY(), then the
formula will return: 20060510
Click to expand...
 
Top