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

nononame2021 261 Reputation points
2024-10-25T08:56:29.37+00:00

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

Microsoft 365 and Office | Excel | For business | Windows
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Jonas Kufner 85 Reputation points
    2024-10-27T19:05:36.67+00:00

    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


  2. Jiajing Hua 18,065 Reputation points Moderator
    2024-10-28T06:18:35.3033333+00:00

    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)))

    User's image

    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.



Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.