Forum Discussion

itz_t_bias's avatar
itz_t_bias
Copper Contributor
Feb 10, 2022
Solved

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 ...
  • HansVogelaar's avatar
    HansVogelaar
    Feb 10, 2022

    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