Excel - how to convert dd-mmm-yyyy to dd/mm/yyyy

Question

My date format is now 01-Oct-2024, how can i use excel formula to change to 01/10/2024 dd/mm/2024

Answer 1

Hi nononame2021,

I would change the cell format: Right klick on the cell / format cell / userdefined. Set the type to TT/MM/JJJJ and Klick on OK.

If the date is actually stored as string I would split the string into parts (day, month, year) and use those parts to form the aimed format like so:

=TEXT(DATE(RIGHT(A1,4), VLOOKUP(MID(A1,4,3), {"Jan",1;"Feb",2;"Mar",3;"Apr",4;"May",5;"Jun",6;"Jul",7;"Aug",8;"Sep",9;"Oct",10;"Nov",11;"Dec",12}, 2, FALSE), LEFT(A1,2)), "dd/mm/yyyy")

I hope that works for you.

Best regards,

Jonas

Answer 2

Hi @nononame2021

Please check whether following formula is helpful.

=DATE(-LOOKUP(0,-LEFT(TRIM(RIGHT(SUBSTITUTE(B5,"-",REPT(" ",100)),100)),4)),MONTH(DATEVALUE(TRIM(MID(SUBSTITUTE(B5,"-",REPT(" ",100)),100,100))&"1")),-LOOKUP(0,-RIGHT(LEFT(B5,FIND("-",B5)-1),2)))

For more details:

Day number: =-LOOKUP(0,-RIGHT(LEFT(B5,FIND("-",B5)-1),2))

Month number: =MONTH(DATEVALUE(TRIM(MID(SUBSTITUTE(B5,"-",REPT(" ",100)),100,100))&"1"))

Year number: =-LOOKUP(0,-LEFT(TRIM(RIGHT(SUBSTITUTE(B5,"-",REPT(" ",100)),100)),4))

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

---