Date "Reversed" Problem in Excel

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Gogs

Thread Starter
Joined
Jul 28, 2003
Messages
121
Hi Guys,

I've imported a CSV file into excel. However one of the columns contains dates which have been exported by the original bespoke software as yyyymmdd or at least in that style (theres no formating applied, despite me saying it was a date field when importing) and it won't let me apply any formatting at all.

Is there an easy way I can "reverse" how its displayed so it I can format it to a ddmmyyyy date? I've tried using MID to extract the characters in the reuqired order but still can't apply any formatting to it. It just comes up as #########. I need it in a recognised date format as I require to do some date orientated calculations after.

thanks in advance

Gogs
 
Joined
Oct 21, 2004
Messages
126
Assuming your 8 digit number (date) is in cell A6, try:

=DATE(LEFT(A6,4),MID(A6,5,2),RIGHT(A6,2))

Then just set the cell format to the appropriate date format you want displayed.
 
Joined
Sep 4, 2003
Messages
4,916
Gogs said:
Hi Guys,
It just comes up as #########. I need it in a recognised date format as I require to do some date orientated calculations after.
Are you sure that the column is not too narrow? Excel often displays these characters in the Column when it is not wide enough to display the contents. Try autofitting the column or manually making it wider.

Rollin
 

Gogs

Thread Starter
Joined
Jul 28, 2003
Messages
121
Thanks for the replies guys.

I used ddockstader's method as the =Text(A6,mm/dd/yyyy) didn't seem to work. Don't know why, it seemed plausable enough.

Thanks once again

Gogs
 
Joined
Aug 17, 2003
Messages
17,584
If the imported records are set as text (commonly the case) then multiplying the cells by 1 often allows susequent formatting as it overrides the set imported formats.

in a spare cell, enter 1
Select the cell, and Edit - Copy
Select the date cells and Edit -Past Special - select "values" and "multiply" and OK

Then try to reformat or any of the above cures.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top