Date conversion

[itz_t_bias]

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

Thanks.

[#2]

@itz_t_bias 

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.

[#3]

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

[#4 · Accepted solution]

@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).

[#5]

Thanks, power query got the job done.