Converting to Dates
 

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

Converting to Dates
 

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

On Nov 1, 9:10 pm, Trazza_UK wrote:
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

Converting to Dates
 

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.

On Thu, 01 Nov 2007 14:10:24 -0700, Trazza_UK
wrote:

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

Converting to Dates
 

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
"Trazza_UK" wrote in message
ups.com...
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

Converting to Dates
 

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

"Trazza_UK" wrote in message ups.com...
| 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
|

Converting to Dates
 

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.
--
Gary''s Student - gsnu2007a


"Trazza_UK" wrote:

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