Converting to Dates

[Trazza_UK]

Hi there,

I have inherited a worksheet whereby dates have been recorded in the
following way:

311007 for 31st October 2007
11107 for 1st November 2007 etc.

I now need to convert these cells into a date format so I can sort the
data and perform calculations. I'd be grateful for any suggestions on
how I can do this.

Paul

 

[Pete_UK]

Can you post a few more examples, showing what happens when you have
dates for September or earlier (single-digit month). Do you get a
leading zero in these cases, like:

10907 for 1st September 2007, 100907 for 10th September 2007?

If you don't, then couldn't your second example be interpreted as 11th
January 2007?

Pete

 

[Myrna Larson]

This is trickier than it should be since the dates aren't always the same
length. Note the leading 0 is missing for the Nov 1 date. If you want to enter
dates this way, you should, before entering the data, format the column as
text and always enter the leading 0. Else, how would you distinguish 1 Nov
from 11 Jan?

But assuming these are numbers and Excel has thrown away the leading 0,

=DATE(2000+MOD(A1,100),MOD(INT(A1/100),100),INT(A1/10000))

If these are text strings, it also works since Excel helpfully converts the
text to numbers on-the-fly.

 

[Otto Moehrbach]

Paul
Here is the problem. 311007 is 31 Oct 07. That is easy to figure out
because the month and the day are both 2 digits and neither can be 3 or more
digits.
But look at 11107. The year is 07. That's easy. But the date could be 1
Nov or 11 Jan.
Do you know something about this data that says this MUST be 1 Nov?
If you can furnish information to eliminate the ambiguity I just described,
then Excel can do what you want. Just imagine you have to instruct someone
on how to do this manually. What would you tell him? HTH Otto

 

[Niek Otten]

Hi Paul,

=DATE(RIGHT(A1,2)+2000,MID(A1,LEN(A1)-3,2),LEFT(A1,LEN(A1)-4))

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi there,
|
| I have inherited a worksheet whereby dates have been recorded in the
| following way:
|
| 311007 for 31st October 2007
| 11107 for 1st November 2007 etc.
|
| I now need to convert these cells into a date format so I can sort the
| data and perform calculations. I'd be grateful for any suggestions on
| how I can do this.
|
| Paul
|

 

[Guest]

In B1:

=TEXT(A1,"000000") and copy down

Now everything in column B is 6 characters, not 5/6

Then in C1:

=DATE(2000+RIGHT(B1,2),MID(B1,3,2),LEFT(B1,2)) and copy down.