Date Format

A

Adam1 Chicago

How can I get Excel (2003) to display a date as YYYYMMDD with no hyphens,
periods or dashes between the YYYY the MM and the DD?

I still need the cell to be recognized as a date.

Thanks
 
A

Adam1 Chicago

Gary,
Thanks -- that get it to the right format. However, I am using the date in
this format as an input to a formula in Excel which pulls some data from
Bloomberg and I need to somehow transform the date into text, because as long
as its VALUE appears as 38398 Bloomberg can't recognize it. It's looking for
the text of 20050223 and I am trying to figure out a way to have my Excel
figure today's date as usual (=today()) then have a macro to cut and paste
that date (which we just formatted as YYYYMMDD) as something that looks the
same but now has the additional property of having a value equal to 20050223.

Thanks,Adam
 
H

HansM

Maybe you can use something like this:

Assuming A1 contains =today(), another cell can contain the correct format
your replace all references to A1 with the formala in A1, but the setup
would be:
=YEAR(A1)&REPT("0",2-LEN(MONTH(A1)))&MONTH(A1)&REPT("0",2-LEN(DAY(A1)))&DAY(A1)

This accounts for months with one digit (Jan-Sep) and days 1 thru 9 of the
month. It will deliver a text value.
 
G

Gord Dibben

Display only?

Format>Cells>Number>Custom YYYYMMDD

23/2/2005 will display as 20050223

Perhaps you have asked the wrong question? If not what you want, please
re-post.


Gord Dibben Excel MVP
 
Top