Hi, 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!

What happens if you try adding 0 to the dates? =A1+0

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

I have tried the DATEVALUEWhat 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.MIKE

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.

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 12Now 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 5In 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

=RIGHT(A1,4)

This gets the Year

Now we put it all back together in cell E1

enter the formula:=DATE(D1,B1,C1)

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.

MIKE

How about this... =DATEVALUE(A1)+0

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

Ask Your Question

Weekly Poll