When a timestamp doesn't behave as a timestamp

**mredekopp** · 03-04-2011, 09:37 PM

I have a long column of data from an Access dump and part of the timestamp cell values are "proper" in that they are really decimal values beneath the date/time formatting while others are just a string of characters that appear to be a timestamp at first glance.

Am I making any sense to anyone? This "issue" escapes my ability to describe it.

You can see a working example on Google Docs at this link.

For lack of words: In column A I have:

Row 1: 02/01/2011 2:20:00 AM
Row 2: 1/31/2011 8:05:00 AM

When I apply a decimal format to the two I get:

Row 1: 40545.0972222222
Row 2:1/31/2011 8:05:00 AM

See that Row 2 doesn't change from date/time format to decimal. It just stays a "dumb" string.

Wha??? Any ideas?

Thanks

Matthew

**JBeaucaire** · 03-04-2011, 09:45 PM

Let's use a "trick" to convert all those text strings into the same Excel decimal date.time values.

1) Highlight the column
2) Select Data > Text To Columns > Delimited > Next > Next
3) On the 3rd screen, select DATE in the upper right corner and set the correct MDY or DMY or whatever it SHOULD be.
4) Click OK.

That should do it.

Now the problem. I don't know if you noticed, but the FIRST row converted but probably converted incorrectly. If your computer's default date format is DMY, then the string converts to the number you gave in example 2 and that date string is actually Jan2,2011, not Feb1,2011.

That's also why the second string doesn't convert, because there is no 31st month. So Excel assumes it's text and leaves it.

So it's up to you when doing the trick I gave you to set the DMY or MDY to what the DATA is, not what you want the display to be. Convert it based on the data so it all converts, which in your sample appears to be MDY. Then set the display to what you want once it's all converted.

**mredekopp** · 03-07-2011, 03:14 PM

Jerry

That's a nifty trick but I'm looking for a solution to the original problem. Doesn't appear quirky that this is happening? I'm having the same problem with a data extraction from a Hobo motor state logger. Some of the timestamps are "dumb" and the others are fine.

Matthew

**ChemistB** · 03-07-2011, 03:39 PM

The reason why some are converting and some dont (as Jerry said) is that your excel is set to look at dates in the format of Day/Month/Year whereas these values are being imported as Month/Day/Year. So as far as Excel is concerned, if the second value is greater than 12, it's not a date.

02/01/2011 (Feb 1) was seen by excel as Jan 2nd, no problem.
1/31/2011 (Jan 31) is undecipherable to Excel because it's looking for the 1st of the 31st month.
Did that help?