Forum Discussion

itz_t_bias's avatar
itz_t_bias
Copper Contributor
Feb 10, 2022

Date conversion

Hi, how do i change the date format from mm/dd/yyyy to dd/mm/yyyy bearing in mind that the cell also has time in it.

So in summary, i want to convert 5/25/2020 1:10:10 PM to 25/5/2020 1:10:10 PM

Thanks.
  • itz_t_bias 

    OK, that suggests that the values are text values.

    With such a value in A2, enter the following formula in the cell next to it:

     

    =LET(v,A2,p,FIND(" ",v),d,LEFT(v,p-1),s_1,FIND("/",d),s_2,FIND("/",d,s_1+1),t,MID(v,p+1,15),DATE(RIGHT(d,4),LEFT(d,s_1-1),MID(d,s_1+1,s_2-s_1-1))+TIMEVALUE(t))

     

    Fill down.

    Or use Power Query (Get and Transform).

  • itz_t_bias 

    Are the values 'real' date/time values? If so, you merely need to apply a custom number format d/m/yyyy h:mm:ss AM/PM to the cells.

    • itz_t_bias's avatar
      itz_t_bias
      Copper Contributor
      Hi, yes they are.

      I applied the custom number format, however nothing changed.

      This is giving me issues with finding date difference because the date format does not match
      • HansVogelaar's avatar
        HansVogelaar
        MVP

        itz_t_bias 

        OK, that suggests that the values are text values.

        With such a value in A2, enter the following formula in the cell next to it:

         

        =LET(v,A2,p,FIND(" ",v),d,LEFT(v,p-1),s_1,FIND("/",d),s_2,FIND("/",d,s_1+1),t,MID(v,p+1,15),DATE(RIGHT(d,4),LEFT(d,s_1-1),MID(d,s_1+1,s_2-s_1-1))+TIMEVALUE(t))

         

        Fill down.

        Or use Power Query (Get and Transform).

Resources