Showing MM:HH as HH:MM:SS when something is over 1 minute

[garypea123]

Hi,

I am not sure if someone can help me, I have received exported data (which I need to try and use)

Here is the thing:

The Data seems to have converted the what should show MM:SS into HH:SS:MM

As an example:

G19 result is 02:45:00
G21 Result is :03

Now whilst G21 seems to be correct, I cam trying to figure out a way to correct G19... I think its just the way the exported data has been sent (which I cannot do anything about)

I did try to but in formula to divide G19 by 60. (then format the cell to mm:ss) - See P19 as answer.

However, this then creates an error in P21 as you cannot divide 3 seconds by 60.

I am not sure if this is understandable

 

[jasonb75]

However, this then creates an error in P21 as you cannot divide 3 seconds by 60.

You can, but your time is not in a valid format which is the reason behind the error. See if this does as needed.

=IF(ISNUMBER(G19),G19,TIMEVALUE(0&G19))

 

[garypea123]

=IF(ISNUMBER(G19),G19,TIMEVALUE(0&G19))

Yes, this works like a dream - Converts everything just as i need to so that I can divide by 2...

The formula is understood