Calaculate time

[Faisal]

I have maintained a Machine maintenance sheet in excel in following format and use the formula given below.We need to convert minute into hours and the formula is =Time(0,Sum(Range1:Range2),0) answer is correct till 1439 minutes and it shows that total breakdown period is 23 min and 59 sec (23.59) but at 1440 min instead of show 24 .00 it shows 0.00 or 1441 min answer would be 24.01 .Cell format is [h]:mm .Sheet format is

Date Machine No Breakdown time in min

xxx 1 20

xxx 1 70

Anser is 1.30 means 90 min

 

[Debraj]

Hi Faisal,


Instead of =Time(0,Sum(Range1:Range2),0), can you please change this to

=CONVERT(SUM(Range1:Range2),"mn","day")

or

=SUM(Range1:Range2)/(60*24)

Dont change cell format [h]:mm ..


Regards,

Deb

 

[Marc L]

Hi Faisal !   Try with cell format [hh]:mm ...

 

[Faisal]

Thanks it works u people are great

 

[Debraj]

@ Faisal..

Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.


@ Marc L..

I think. "[HH]:MM" will show "7:34" to "07:34", but I don't think, it has any effect in case of 2 digit Hour.. Please correct me if I was wrong, and don't hesitate to shout on me..


Regards,

Deb

 

[Marc L]

Yes you're right, I misanderstood the question …


But with the cell format [h]:m on my computer I have 24:00 and not the 0:00 of Faisal …

Maybe 'cause I'm on a 24h time and not a US 12h time with AM & PM …

 

[Faisal]

Dear All


Convert formula of above mentioned query is not compatible with office 2003. Kindly update me how can use the same formula in office 2003.

 

[sdsurzh]

Hi Faisal,


Suppose in A1 you have 1440 (In Minutes) then in A2 please apply the following formula =A1/1440 and change the format to “[hh]:mm:ss;@” it will work.


Thanks,

Suresh Kumar S

 

[Faisal]

thanks it works