SOLVED

Date conversion

%3CLINGO-SUB%20id%3D%22%5C%26quot%3Blingo-sub-3149742%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3EDate%20conversion%26lt%3B%5C%2Flingo-sub%26gt%3B%3CLINGO-BODY%20id%3D%22%5C%26quot%3Blingo-body-3149742%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3EHi%2C%20how%20do%20i%20change%20the%20date%20format%20from%20mm%2Fdd%2Fyyyy%20to%20dd%2Fmm%2Fyyyy%20bearing%20in%20mind%20that%20the%20cell%20also%20has%20time%20in%20it.%3CBR%20%2F%3E%3CBR%20%2F%3ESo%20in%20summary%2C%20i%20want%20to%20convert%205%2F25%2F2020%201%3A10%3A10%20PM%20to%2025%2F5%2F2020%201%3A10%3A10%20PM%3CBR%20%2F%3E%3CBR%20%2F%3EThanks.%26lt%3B%5C%2Flingo-body%26gt%3B%3CLINGO-LABS%20id%3D%22%5C%26quot%3Blingo-labs-3149742%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CLINGO-LABEL%3EExcel%26lt%3B%5C%2Flingo-label%26gt%3B%3CLINGO-LABEL%3EFormulas%20and%20Functions%26lt%3B%5C%2Flingo-label%26gt%3B%26lt%3B%5C%2Flingo-labs%26gt%3B%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E
New Contributor
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.
4 Replies

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

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
best response confirmed by itz_t_bias (New Contributor)
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).

Thanks, power query got the job done.