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?

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.

Just the month is 1 or 2 digits. The day is always 2 and the year is always 4.

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

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.

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

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History