Timeformat Sum Up and Separation Based on Conditions

[r_know]

Dear All,

In my previous thread, I had asked the sum up based on segregation as per the total of 24 in first column and next column carry forward.

Now, in my sheet, I have changed 24 not as value but as time hrs:mm. (24:00), in column E.

Now below formula does not work, can you suggest how to modified for time format [h]:m.


=IF(SUM($E$4:$E4)<=24,MIN(24,SUM($E$5:$E5))-SUM($E$4:$E4),"")
and

=IF(SUM($E$5:$E5)<=24,"",E5-N(G5))

Regards,
RL>

http://www.vbaexpress.com/forum/showthread.php?t=42609

[Bob Phillips]

Change the formulas to


=IF(SUM($E$4:$E4)<=1,MIN(1,SUM($E$5:$E5))-SUM($E$4:$E4),"")

and


=IF(SUM($E$5:$E5)<=1,"",E5-N(G5))

and format the cells as [h]:mm

[r_know]

Thanks A Lot, Once Again!

I changed the Value 24 to 1, & Work Great. But what is fundamental behind this?

Can you pls teach me this fundamental in very short!!!

[Bob Phillips]

Time is a fraction of one day, hence the 1.

[r_know]

So for understanding!

12 hrs = 0.5
24 hrs = 1

Right?

[Bob Phillips]

Dead right.