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

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
--
AP

"nick" a écrit dans le message de news:
...
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

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

If A1 contains a date then:

=TEXT(A1,"yyyymmdd")
--
Gary's Student


"nick" wrote:

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

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




"nick" wrote:

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

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


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=540723

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

--
Gary''s Student


"nick" wrote:

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

Thank You all....

"Gary''s Student" wrote:

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

--
Gary''s Student


"nick" wrote:

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