Forum Discussion
itz_t_bias
Feb 10, 2022Copper Contributor
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 ...
- Feb 10, 2022
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
Feb 10, 2022Copper 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
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
Feb 10, 2022MVP
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_biasFeb 10, 2022Copper ContributorThanks, power query got the job done.