MS Excel date formatting after export

June 30, 2013 at 21:43:37
Specs: Windows 7

I am having a seemingly popular issue regarding the format of dates when exporting data to Excel. I have been trying to export timesheet data from a program called Replicon into Excel, however, once exported, I cannot change the format of the dates. I have tried the DATEVALUE and the Text-to-Columns suggestions and neither have worked.

When exported to Excel, the dates are in this format - May 30, 2013

Any other ideas? Thanks, I appreciate the help!

See More: MS Excel date formatting after export

July 1, 2013 at 04:06:25
What happens if you try adding 0 to the dates?


Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

July 1, 2013 at 12:58:48
I have tried the DATEVALUE

What happened when you tried DATEVALUE?
Did you get a five digit number like 41424?

Try using =TRIM(CLEAN(A1))
This will remove any extra space characters
and any non printable characters that may
have gotten imported with the data.


Report •

July 1, 2013 at 18:28:11
Thanks for the replies!

I tried adding 0 to the dates and it came up with an error in value - #VALUE!

This same error also occurred when I entered the DATEVALUE formula.

I tried =TRIM(CLEAN) as this didn't work either!

Thanks again for the help.

Report •

Related Solutions

July 1, 2013 at 19:22:05
The easiest way, I can think of, to convert your TEXT string date
would be to use a short lookup table.

Try this:

Create a Lookup Table on a new sheet called Dates:

Sheet Dates

         A      B
 1) January     1
 2) February    2
 3) March       3
 4) April       4
 5) May         5
 6) June        6
 7) July        7
 8) August      8
 9) September	9
10) October	10
11) November	11
12) December	12

Now on Sheet 1, with your TEXT string Date in cell A1

In cell B1 enter the formula:

=VLOOKUP(TRIM(LEFT(A1,FIND(" ",A1,1))),Dates!A1:B12,2,0)

This will convert your Named Month IE May
into a Number Month IE 5

In cell C1 enter the formula:

=LEFT(RIGHT(A1,LEN(A1)-FIND(" ",A1,1)),FIND(" ",RIGHT(A1,LEN(A1)-FIND(" ",A1,1)),1)-2)

A rather longish formula to cut out the Day part of the date, IE 30

In cell D1 enter the formula


This gets the Year

Now we put it all back together in cell E1
enter the formula:


The format of the DATE function is, YEAR MONTH DAY

You should now have a real date.

If your really adventerous you could also simply combine all three formulas in one very long one and simply plug that into the =DATE() function:

=DATE(RIGHT(A1,4),VLOOKUP(TRIM(LEFT(A1,FIND(" ",A1,1))),Dates!A1:B12,2,0),LEFT(RIGHT(A1,LEN(A1)-FIND(" ",A1,1)),FIND(" ",RIGHT(A1,LEN(A1)-FIND(" ",A1,1)),1)-2))

See how that works for you.


Report •

July 1, 2013 at 19:38:06

Report •

Ask Question