Forum Discussion
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 ...
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).
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
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
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).
- Thanks, power query got the job done.