Is there an Excel date format as follows: yyyymmdd?

[Guest]

This format is used by our database and makes it a bit of a problem uploading
Excel sheets into it. I was wondering if there was a formula that might be
able to make this adjustment?

Thanks

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...6-14b170c69d31&dg=microsoft.public.excel.misc

 

[Nick Hodge]

N

It's actually ISO standard format but XL doesn't recognise it, depending on
how your locale is set up (In the UK it's dd/mm/yyyy) then I would use the
formula

=DATEVALUE(RIGHT(A1,2)&"/"&MID(A1,5,2)&"/"&LEFT(A1,4))

Formatted as a date (dd/mm/yyyy), with the imported date in A1 and copied
down

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)

 

[JennieJ]

You could try formatting the cells by going into "format", "cells", then
down to "custom". If you type "yyyymmdd" (without the quotes) into the
Type box (it will probably default to "general") and then enter 6/20/06
into that cell it will display as 20060620. If you need "/" marks make
the format type "yyyy/mm/dd", enter 6/20/06 into the cell as a test,
and you'll get 2006/06/20.

You can enter your information as 6/20/06; June 20, 2006; or June 20,
and the you'll get the same displayed result -- either 20060620 or
2006/06/20.

 

[Owen]

symantics, but for my eye this is a bit more simplified

DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))