Forum Discussion
Date conversion
- 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).
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_biasFeb 10, 2022Copper ContributorHi, 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- HansVogelaarFeb 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.