please help

[Joe]

I have a sheet that recaps hours of service. But the rules have changed and
I cant figure how to do this now

B10 thru 41 = day of month
1 - 31

C10 thru 41 = hours worked that day
entered manually

D10 thru 41 = total hours on duty in the last 7 days
=IF(AND(ISBLANK(C10)),"",(F10-C3))

E10 thru 41 = available hours
=IF(AND(ISBLANK(C10)),"",(70-D10))

F10 thru 41 = total on duty in last 8 days
=IF(AND(ISBLANK(C10)),"",(C10+D9))

what I need is a way that when there are 34 or more hours off duty to reset
the available hours back to 70. and restart the count from 70. It should
also restart the on duty (D10 - 41) and on duty (F10 - 41) as well.

I can add another column or two to the if need be to get this

Thanx Joe

 

[Mark Graesser]

Joe,
How do you determine that the person has been off or 34 hours? Do you mean that they have been off for 1 day and 10 hours, or a certain number of shifts?

I have recreated your table to test out some options. Do rows 2 through 9 contain hours from the end of the previous month?

Regards,
Mark Graesser
(e-mail address removed)

----- Joe wrote: -----

I have a sheet that recaps hours of service. But the rules have changed and
I cant figure how to do this now

B10 thru 41 = day of month
1 - 31

C10 thru 41 = hours worked that day
entered manually

D10 thru 41 = total hours on duty in the last 7 days
=IF(AND(ISBLANK(C10)),"",(F10-C3))

E10 thru 41 = available hours
=IF(AND(ISBLANK(C10)),"",(70-D10))

F10 thru 41 = total on duty in last 8 days
=IF(AND(ISBLANK(C10)),"",(C10+D9))

what I need is a way that when there are 34 or more hours off duty to reset
the available hours back to 70. and restart the count from 70. It should
also restart the on duty (D10 - 41) and on duty (F10 - 41) as well.

I can add another column or two to the if need be to get this

Thanx Joe

 

[Joe]

Joe,
How do you determine that the person has been off or 34 hours? Do you

mean that they have been off for 1 day and 10 hours, or a certain number of
shifts?
Column C is where the hours are entered. and if the houurs are 0 for one day
and less than 14 for the next we can assume 34 hrs off for both days.

I have recreated your table to test out some options. Do rows 2 through 9

contain hours from the end of the previous month?

almost, 3 thru 9 do. Its a seven day recap

 

[Mark Graesser]

Joe
Try this out in cell E1

=IF(AND(ISBLANK(C10)),"",IF(AND(C10=0,C9<14),70,(E9-C10))

This will keep a running total of available hours and will only reset if the person hase the current day off and worked less then 14 hours on the previous day. If they don't have any time off it will go negative. Is this OK

If the negative is okay, you could also use conditional formatting to highlight the cell for a prominent visual indicator

Good Luck
Mark Graesse
(e-mail address removed)


----- Joe wrote: ----

Joe
How do you determine that the person has been off or 34 hours? Do yo

mean that they have been off for 1 day and 10 hours, or a certain number o
shifts


Column C is where the hours are entered. and if the houurs are 0 for one da
and less than 14 for the next we can assume 34 hrs off for both days

I have recreated your table to test out some options. Do rows 2 through

contain hours from the end of the previous month

almost, 3 thru 9 do. Its a seven day reca

 

[Joe]

Joe,
Try this out in cell E10

=IF(AND(ISBLANK(C10)),"",IF(AND(C10=0,C9<14),70,(E9-C10)))

This will keep a running total of available hours and will only reset if

the person hase the current day off and worked less then 14 hours on the
previous day. If they don't have any time off it will go negative. Is this
OK?


Not yet ... what happens is the first day off ( first zero) entered resets
the hours back to 70. before entering the next days hours. and entering the
second days hours keeps a running total from the first day ( first 0
entered) I tried a few variations on the formula but NG.

(C10+C9<=14)
(C10+C9>=33)
(C10+C9<14)

still puzzling it though

If the negative is okay, you could also use conditional formatting to

highlight the cell for a prominent visual indicator.