[Closed] Quick MS Excel question

For the example you give

=DATE(RIGHT(A1,4),MID(A1,3,2),LEFT(A1,2))

Where A1 is the cell you have the number in.

If you don't have leading zeros use

=DATE(RIGHT(TEXT(A1,"00000000"),4),MID(TEXT(A1,"00000000"),3,2),LEFT(TEXT(A1,"00000000"),2))

it will work but there is probably a more elegant solution.

Use "text to columns" & "fixed width" to split the data to 3 columns of "02" "09" and "2011".

Then in Column D1 enter =a1&"/"&b1&"/"&c1. Drag formula down, copy column & paste special; values. Format column to date format.

Edit - ignore me. Post above is much better.

Doesn't seem to work for some reason

What result do you get?

=DATEVALUE(cells) ?

Won't changing the cell format to date work?

Edit, er no. Nearly but returns the year 7694 instead of 2011.

That's why you need DATEVALUE. DATEVALUE converts it to a string that Excel understands as a date, then you Copy and Paste Special / Values the resultant cells.

I think. Something like that anyway.

Cougar, I don't think DATEVALUE will recognise the OP's number as date text.

Mash the text into a date format by whatever method, then do copy, paste values or paste values and format.

Lots of functions will not work with the output of other functions..

Oh, and Leading Zeroes don't exist in excel (if you can see them, that's because someone's set the number format as 00000000 - press Ctrl 1 to see it)...

You'll need to work from the right to get it into the right format. Take the first 4 digits on the right to get the year, then the next two to establish the month, then whatever's left to get the first digit. Not got the time to work out the formulae, but I'd do it with a load of columns (ie, one for year, one for month etc.), using LEFT, RIGHT, MID and LEN functions.

Left takes a chunk off the left, Right- I think you can guess, and Mid takes a chunk out the middle.

Len gives you the length of whatever's in the cell so you can still make things work if the length of your string varies.

An alternate method would be to concatenate a 9 (all months should be at most 31 days long) onto the front of every leading zero date, and then strip that out later. This would mean you could just take the left two digits, the middle 2 digits, and the last 4 digits using pretty simple formulae.

You'd get your 9 with something like =if("(Len(A2)<8"),(concatenate("9",A2),) if your first date is in A2. Copy and paste values again to strip out the formulae...

You'd then do another if statement to pull out the right hand digit if the number starts with a 9...

Cougar, I don't think DATEVALUE will recognise the OP's number as date text.

Aha. You may have a point there. (-:

In that case, I'm oot.

Worked it out. Drop me an email.

Is it a secret?

Nope, it's just a load of columns with LEFT, RIGHT and LEN functions and it's a bit hard to follow from cut and paste...

Set out the below across Row 1 - the | sign is there to show the breaks between columns:
Original | Year | Month |Length | Day | Date | Paste values

Your date goes into cell A2...

Cell B2:
=RIGHT(A2,4)

C2:
=LEFT((RIGHT(A2,6)),2)

D2:
=LEN(A2)

E2:
=LEFT(A2,(D2-6))

F2:

=CONCATENATE(E2,"/",C2,"/",B2)

You then copy the contents of column F and Paste Values into Column G.

Set the format as Date or Datestring it, and you're sorted.

All the above could be condensed into one formula, but it'd make your brain ache...

=DATEVALUE(((LEFT(A1, 2)&"/"&RIGHT(LEFT(A1, 4), 2)&"/"&RIGHT(A1, 4))))

As usual, be patient and Stoner will pop up with one of his rather nice solutions 😀

Stoner shouldn't that be a 2 instead of the first 4?

matt - no. Im carving 2 chars from the right side of a 4 char carve to the left. i.e. the 3rd and 4th chars from a n length chain.

ps , dont forget to format the formula cell in date format or you just get the numerical date value.

pps: pennine, too kind 🙂