How many hours in the last 7 days did I work?
 

I need help putting together a formula for these rolling time periods please.
I have hour limits on time worked. I can:
-Work no more than 16 hours straight. And,
-Work no more than 16 in any 24 hours. And
-No more than 28 in any 48 hours. And
-No more than 72 hours in 7 days. And
-At least 8 hours between shifts.
Very limiting but I've been unable to figure a formula/formulas using time
of the day you worked - or scheduled to be worked. Thanks

How many hours in the last 7 days did I work?
 

Hi,

You're having a hard time because you want a rolling time period and in
effect the spreadsheet formula to do that, if there were one, would exceed
the character limit on a spreadsheet formula. The best way to approach this
is using VBA - try creating a custom function.

What do you do with hour that exceed any of the rules? What about weekends
and holidays? Can work extend across the midnight hour?

Cheers,
Shane Devenshire


"Sewfoolish" wrote:

I need help putting together a formula for these rolling time periods please.
I have hour limits on time worked. I can:
-Work no more than 16 hours straight. And,
-Work no more than 16 in any 24 hours. And
-No more than 28 in any 48 hours. And
-No more than 72 hours in 7 days. And
-At least 8 hours between shifts.
Very limiting but I've been unable to figure a formula/formulas using time
of the day you worked - or scheduled to be worked. Thanks

How many hours in the last 7 days did I work?
 

Thanks for the reply... No wonder I couldn't figure it out!

I work at a Nuclear Station and the NRC won't allow you to work excessive
hours (and aren't you glad:) ) Actually, the rules are even more restrictive
on time (time to turnover work to the next shift doesn't count), but this
will get me really close. We have to fill out a request to exceed the limits
by even 5 minutes. It would be great if I had a way to look ahead to see
easily/fast what time I might need to leave if I don't want to fill out a
request and talk to a VP. Or to take a beating (lol) when I do miscalculate
(that pesky lunch time trips me up). With that many criteria, it's easy to
get mixed up.

As for the hours, it runs across weekends, holidays, and midnight.

Thanks for the help.
.........
"Shane Devenshire" wrote:

Hi,

You're having a hard time because you want a rolling time period and in
effect the spreadsheet formula to do that, if there were one, would exceed
the character limit on a spreadsheet formula. The best way to approach this
is using VBA - try creating a custom function.

What do you do with hour that exceed any of the rules? What about weekends
and holidays? Can work extend across the midnight hour?

Cheers,
Shane Devenshire


"Sewfoolish" wrote:

I need help putting together a formula for these rolling time periods please.
I have hour limits on time worked. I can:
-Work no more than 16 hours straight. And,
-Work no more than 16 in any 24 hours. And
-No more than 28 in any 48 hours. And
-No more than 72 hours in 7 days. And
-At least 8 hours between shifts.
Very limiting but I've been unable to figure a formula/formulas using time
of the day you worked - or scheduled to be worked. Thanks