Forum Discussion
Date conversion
So in summary, i want to convert 5/25/2020 1:10:10 PM to 25/5/2020 1:10:10 PM
Thanks.
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).
4 Replies
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.
- 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 matchOK, 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).
