PDA

View Full Version : Timeformat Sum Up and Separation Based on Conditions



r_know
07-20-2012, 02:30 PM
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
07-21-2012, 01:34 AM
Change the foormulas 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
07-21-2012, 07:09 AM
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
07-22-2012, 11:22 AM
Time is a fraction of one day, hence the 1.

r_know
07-22-2012, 11:23 AM
So for understanding!

12 hrs = 0.5
24 hrs = 1

Right?

Bob Phillips
07-22-2012, 02:27 PM
Dead right.