kryptonian
Board Regular
- Joined
- Oct 6, 2006
- Messages
- 104
I have a report that generates a date in this format - 12/6/2008. In excel, this is interpreted as December 6, 2008 in month/day/year format.
That's my problem. It shouldn't be interpreted as such and should be interpreted as June 12, 2008. The report comes from a client so that rules out changing the date format from the report source.
I've been tinkering with using LEFT/MID/LEN combinations but I have only managed to extract the first part of the date, which in this example is 12. The formulas I used were:
=LEFT(TEXT(A1,"m/d/yyyy"),FIND("/",TEXT(A1,"m/d/yyyy"))-1) and
=MID(TEXT(A1,"m/d/yyyy"),1,FIND("/",TEXT(A1,"m/d/yyyy"))-1)
How can I extract the text between the two "/"?
I am planning to just use concatenate afterwards to re-arrange the numbers extracted from this cell.
If someone has a better way to go around this problem, my thanks in advance!
That's my problem. It shouldn't be interpreted as such and should be interpreted as June 12, 2008. The report comes from a client so that rules out changing the date format from the report source.
I've been tinkering with using LEFT/MID/LEN combinations but I have only managed to extract the first part of the date, which in this example is 12. The formulas I used were:
=LEFT(TEXT(A1,"m/d/yyyy"),FIND("/",TEXT(A1,"m/d/yyyy"))-1) and
=MID(TEXT(A1,"m/d/yyyy"),1,FIND("/",TEXT(A1,"m/d/yyyy"))-1)
How can I extract the text between the two "/"?
I am planning to just use concatenate afterwards to re-arrange the numbers extracted from this cell.
If someone has a better way to go around this problem, my thanks in advance!