Solved Raw Data format to Date format

August 16, 2012 at 07:45:29
Specs: Windows XP
I have a listing of "Dates" that are written as one number (i.e. for todays date it would be 8162012). I need to put it in a date format that excel recgonizes (i.e. 8/16/2012). I need to be able to prefor this task quickly for thousands of lines. I was toying with the Date formula using left, mid and right, but the problem is the mmddyyyy position changes some with the format that it is in. For example, todays date (8/16/2012) is written as 8162012, but 11/30/2012 would be written 11302012. Any thoughts other than trying to change the source formatting?

See More: Raw Data format to Date format

Report •


#1
August 16, 2012 at 11:25:44
You have pointed out that the month might be 1 or 2 digits, which is fairly easy to deal with, if that's the only issue.

However, you didn't say anything about the day. Will they also be 1 or 2 digits? If so, then we have a problem.

If both the month and day can be one or 2 digits, there no way for anyone to know what 1112012 means.

Is that 11/1/2012 or 1/11/2012?

If that's the case, then I think you need to deal with the source data, unless you have some other way of knowing what to do with dates like 1212012, 1142012, etc.

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


Report •

#2
August 17, 2012 at 04:29:45
Just the month is 1 or 2 digits. The day is always 2 and the year is always 4.

Report •

#3
August 17, 2012 at 08:18:24
✔ Best Answer
Just the month is 1 or 2 digits. The day is always 2 and the year is always 4.

If that is so, then try this rather unconventional way:

=--TEXT(A1,"00-00-0000")

There are two dash marks before the word TEXT, just copy and paste from here.

See how that works.

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
August 17, 2012 at 16:05:54
But when I use your formula, I get a 5 digit number. ;-)

Nice one, Mike ;-)

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


Report •

#5
August 17, 2012 at 17:05:39
Thanks, but I can't claim it as original.
Picked it up in my travels around the web.

It is a rather nice piece though.

MIKE

http://www.skeptic.com/


Report •

Ask Question