Forum Discussion

vivienkong's avatar
vivienkong
Copper Contributor
Mar 06, 2023
Solved

changing numbers into date

I want to change the number 28022023 into 28/02/2023 format. Kindly advice how how to do it. The number is actually a transaction date provided from a statement. I want to format it into 28/02/2023 s...
excel
  • Patrick2788's avatar
    Patrick2788
    Mar 06, 2023

    vivienkong 

    Let's say the number is in A1:

    =DATE(RIGHT(A1,4),MID(A1,3,2),LEFT(A1,2))

    Then choose desired cell formatting for date.

vivienkong's avatar
vivienkong
Copper Contributor
Mar 10, 2023
The above formula works for date 10 to 31. How to re-word the formula for date 1 to 9?
12052021 12/5/2021
12052021 12/5/2021
29042021 29/4/2021
6042021 30/7/2024
6042021 30/7/2024
Patrick2788's avatar
Patrick2788
Silver Contributor
Mar 10, 2023
For those last two dates, I don't see where 2024 is coming from. Those appear to be 2021 dates?
  • vivienkong's avatar
    vivienkong
    Copper Contributor
    Mar 10, 2023
    for the date is ddmmyyyy (which the date is from 10 to 31) , the formula works fine.
    for date that is dmmyyyy (which the date is from 1 to 9), the formula jumps 24 days, 3 months and 3 years ahead

Resources