MS Excel date formatting after export

June 30, 2013 at 21:43:37
Specs: Windows 7
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!


See More: MS Excel date formatting after export

Report •


#1
July 1, 2013 at 04:06:25
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.


Report •

#2
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.

MIKE

http://www.skeptic.com/


Report •

#3
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

#4
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

=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

http://www.skeptic.com/


Report •

#5
July 1, 2013 at 19:38:06

Report •


Ask Question