Some data i export to Excel appears like this: '3 Days, 2:15:37' (when the period exceeds 24 hours only, [hh:mm:ss] otherwise, which is ok). How can i display it on Excel as [hh:mm:ss] even when it exceeds 24 hours?

CodePudding user response：

Maybe you'll find a better method, but here's one method that involves using some helper columns. Since your column is of General format, then it's going to recognize some of your cells as Time except for those with "Days". You have a mixture of formats so it's a but cumbersome.

You can probably consolidate a couple steps here, but breaking it out better identifies a process. Column A is your original data.

Column B: Formula to extract data to the right of "comma space":

=IFERROR(MID(A2,FIND(",",A2) 2,10),A2)

Column C: For cells containing the word "Day", take the value to the left of it and multiply by 24.

=IFERROR(LEFT(A2,FIND("Day",A2)-1)*24,0)

Column D: Extract the HOURS from your time format of column B.

=HOUR(B2)

Column E: Take the MM:SS from column B.

=TEXT(B2,"mm:ss")

Column F: Put it all together:

=CONCAT(C2 D2,":",E2)

Here's one way to combine the last two steps, which would reduce the number of helper columns by one.

=CONCAT(C2 D2,":",TEXT(B2,"mm:ss"))