calculating meals per 24 hour period
Posted by Richard Laing on September 29, 2000 5:48 PM
I need to calculate the number of meals per 24 hour period. Up to three hours no meals are allowed. Between 3 and 6 hours, 1 meal is allowed, between 6 and 9 hours, 2 meals etc. to a maximum of 4 meals per 24 hours. After 24 hours, the calculations are reset. The times are calculated on one sheet and the meals are calculated on another. The formula I have used is =IF(B14<>"",IF('Task Report Form'!$P$13>0.0208333,ROUNDUP('Task Report Form'!$P$13,0),0),0)where 0.0208333 is the result of (DATE COMPLETED+TIME COMPLETED)-(DATE STARTED+TIME STARTED)and B14 is valid only if a name is inserted. I am having problems with it giving me 1 meal for less than 3 hours work. I am still struggling to understand how this all works, so I hope this makes sense.
Posted by Celia on September 29, 2000 7:38 PM
Richard
Heres one way :-
Assumptions :-
1.Sheet1 contains the times.
2.The differences between the start times and end times have already been calculated on Sheet1, and they are in the format "h:mm" starting in cell A1.
3.On Sheet2, there are persons' names in column B starting in B1
On Sheet2, enter the following in some blank cells (let's say in cells G1:H8) :-
G1 to G8 - 3,6,9,12,15,18,21,24
H1 to H8 - 0,1,2,3,4,4,4,4
In cell C1 of Sheet2 enter the following formula and fill down as far as required :-
=IF($B1="","",VLOOKUP(CEILING(Sheet1!$A1*24,3),$G$1:$H$8,2))
Post again if any problems.
Celia
Posted by Richard Laing on October 01, 2000 10:23 AM
Thanks Celia
Celia
Thank you very much. What you sent works just fine. I also continued the sequence on to calculate for 48 hours. I appreciate your help.
Richard
