# Date "Reversed" Problem in Excel



## Gogs (Jul 28, 2003)

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


----------



## coachdan32 (Nov 13, 2003)

Have you tried =text(mm/dd/yyyy)


----------



## ddockstader (Oct 21, 2004)

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.


----------



## Rollin_Again (Sep 4, 2003)

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 (Jul 28, 2003)

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


----------



## kiwiguy (Aug 17, 2003)

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.


----------

