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

Do you think manufacturers should do more to reduce phone and tablet usage among kids?

Discuss in The Lounge

Poll History