Need to change date/time format on 2 columns of data

[alexx1202]

How do I format the date and time shown in the columns boxed in red to display like the one boxed in green? (Refer to pics)

I want both columns to look like e.g 24/11/2009  9:32:00 PM not 2009.11.24 21:32:00.

Thanks

[#2]

@alexx1202 could try using format painter.

[#3]

@alexx1202 The date/time stamps in the first picture seem to be real time values that are custom formatted to display the way they do. So, these you can easily change simply by choosing the time format you want, for instance dd/mm/yyyy hh:mm:ss am/pm

In the other picture the date/time stamps seem to be texts, so you need to change them to date values first. Dates are in fact numbers counting from 1 for 1 Jan 1900. So, 24 Nov 2009 is actually day number 40141. And the time 21:32:00 is represented as a fraction of a 24 hour day, thus, 0.897222222222222. Put them together to get a date/time value of 40141.897222222222222.

Use Text-to-columns on the Data ribbon to transform the texts to date/time values. Select the range of dates in column E. Then, Data, Text to columns and just press Finish. Repeat for column I. Now you should have date/time values formatted in the default date/time format for your system. Change the  format as you wish as described above.

[#4]

Hi @Riny_van_Eekelen 

Tried what you suggested but no luck. See video attached.

[#5]

@alexx1202 Which Excel version are you on? Perhaps you need to advance to step 3 in Text-to-columns and specify that it's a Date in the DMY format.

[#6]

Thanks @Riny_van_Eekelen 

How do I change these cells showing time durations in 0.0 format to a 00:00 one that reads as months : weeks : days : hours ; minutes : seconds?

I can get dd : hh : mm : ss but some cells show durations over 30 days (1 month). When that happens the clock resets e.g. 32 days gets displayed as 02:00:00:00.

[#7]

@alexx1202 I never came across this issue but I Googled a bit and found the answer to your question in the link below:

https://exceljet.net/formulas/time-duration-with-days