Is there an Excel date format as follows: yyyymmdd?
 

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/comm...lic.excel.misc

Is there an Excel date format as follows: yyyymmdd?
 

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
HIS


"N" wrote in message
...
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/comm...lic.excel.misc

Is there an Excel date format as follows: yyyymmdd?
 

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.


--
JennieJ
------------------------------------------------------------------------
JennieJ's Profile: http://www.excelforum.com/member.php...o&userid=35576
View this thread: http://www.excelforum.com/showthread...hreadid=554749

Is there an Excel date format as follows: yyyymmdd?
 

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

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


Nick Hodge wrote:
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
HIS


"N" wrote in message
...
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/comm...lic.excel.misc