Taylor Campbell4 said:I'm having a nightmare with this formula
Code:
=DATE(MID(R441;6;4);MID(R441;1;1);MID(R441;3;2))
Basically, the dates in my text look like this sometimes:
10/1/2012
but other times they look like this:
9/25/2012
And I can't change how they're written.
So, sometimes the month is just one digit, sometimes it's two.
The day might be three characters, four, or five depending on whether the month and day are single or double digits. Makes sense?
How the hell am I supposed to fix this???
Look, if those dates of yours are actually:
October 1, 2012 and
September 25, 2012,
meaning they're written in
month/day/year format, then this is the formula you need:
=DATE(RIGHT(R441;4);LEFT(R441;FIND("/";R441)-1);MID(R441;FIND("/";R441)+1;LEN(R441)-LEN(RIGHT(R441;4))-LEN(LEFT(R441;FIND("/";R441)-1))-2))Here's the breakdown:
=DATE(year;month;day)
year = reading right to left: the first four characters of the string in cell R441
month = reading left to right: the first n characters of the string in cell R441 where
n = FIND("/";R441)-1
The FIND function locates the index
of the first "/" found and then we subtract 1 from that number to ditch the slash from the calculation.
day = within the string in cell R441: everything between x and y where
x = everything after the first slash + 1
y = the total length of the string in cell R441 minus the length of the year, minus the length of the month, and minus 2 because there are two slashes in the string total.