LinkBack URL
About LinkBacks
I get excel sheets with dates like the following and have to constantly convert them to another date format: 190501 and want to create a formula to automatically convert this to 05/01/2019.
I get excel sheets with dates like the following and have to constantly convert them to another date format: 190501 and want to create a formula to automatically convert this to 05/01/2019.
Welcome to the forum!
Try this:
=DATE(YEAR(2000+LEFT(A1,2)),MONTH(MID(A1,2,2),DAY(RIGHT(A1,2))
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Or maybe...
=(MID(A1,3,2)&"/"&RIGHT(A1,2)&"/"&LEFT(A1,2))+0 >> format as data how you desire
@Ali, your submission seems to be missing a parenthesis and the mid function should be 3 and not 2
HTH
Regards, Jeff
TryFormula:
Please Login or Register to view this content.
Or use text to columns, changing the date format dropdown in the last tab to YMD
Thanks, Jeff.
=DATE(YEAR(2000+LEFT(A1,2)),MONTH(MID(A1,3,2)),DAY(RIGHT(A1,2))
You might want to test that formula, Ali
Another way:
B2: =--TEXT(A1, "2\000-00-00")
Last edited by shg; 05-29-2019 at 02:44 PM.
Entia non sunt multiplicanda sine necessitate
You seem to be enjoying catching me out today, Jason!
You've corrected it in post #4, so no need for me to bother.
Not deliberately, honest guv! You just seem to be having an off day, I'll try to avoid looking at any more of your answers, just in caseOriginally Posted by AliGW
You seem to be enjoying catching me out today, Jason!
I think the rep in ths thread has to go to shg for the simple and elegant solution that we all missed though.
Last edited by jason.b75; 05-29-2019 at 01:44 PM.
Yup - one or two gaffs, but actually quite a lot that have been spot on today for me!!!
Kudos to SHG.
Alternatively, you can just use text to columns & select YMD
This helped! Thank you so much!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks