GaryK

OK I need to convert a value of hh:mm:ss in a cell to total number of minutes. The cell is formatted as custom hh:mm:ss.

Now logically I would think that just doing a VALUE(LEFT(A1,2)) * 60 + VALUE(MID(A1, 4, 2)) would work. No it doesnt! Bizarrely its returning the wrong values. If I have a value of 10:20:00 and issue =MID(A1,4,2) you would expect the value returned to be '20', well its '30'! And doing =LEFT(A1,2) returns '0.' and not '10'.

Can anyone help and give me what the formula should be?

Cheers guys

Gary

Dream Weaver

Look into using the functions, there are specific date & time functions you can use, hour, minute, timevalue etc.

These work

GaryK

DW,

Ive looked at them, done the RTFM STFW not much joy! This must be a common problem that must have been solved by someone?

Gary

markr1963

Here ya go. A1 contains 10:20:00

=HOUR(A1)*60 + MINUTE(A1) gives 620

HTH
Mark

Dream Weaver

thats what I meant

GaryK

thanks guys, having a dumb day

Gary