Date formatting

[Guest]

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

 

[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

 

[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")

 

[Guest]

If A1 contains a date then:

=TEXT(A1,"yyyymmdd")

 

[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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

Thank You all....

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