Outcomes dependant on formula

[Colin Weir]

Hi There

I developed a time sheet for my organisation using Excel recently. Since doing this there has been a policy change that I'd like to incorporate into the time sheet. We work on a Flexi system where we can build up hours to take time off later. We would like it changed to where after a four week period if someone has more than a certain amount of hourse that they would loose the excess.

Basically I work full time so I can carry 21 hours, however if I have 23 hours at the end of a four week period it would automatically reduce to 21 hours.

The problem I have is that some people work part time and they are only allowed to carry forward an equivalent of two days.

I currently have a field on it where people insert their number of hours which then formats formulas throughout the sheet. I would like it that this would still be the case.

I hope this makes sense.

Regards

Colin

 

[Cimjet]

Hi Colin
We can't see your layout or your formulas but maybe this can send you in the
right direction.
=MIN(A4+B4,21) if you use the function '=MIN()" as per sample, it will add your
range but max out at 21.
you'll need to play with it.
HTH
Cimjet
Hi There

I developed a time sheet for my organisation using Excel recently. Since doing
this there has been a policy change that I'd like to incorporate into the time
sheet. We work on a Flexi system where we can build up hours to take time off
later. We would like it changed to where after a four week period if someone
has more than a certain amount of hourse that they would loose the excess.

Basically I work full time so I can carry 21 hours, however if I have 23 hours
at the end of a four week period it would automatically reduce to 21 hours.

The problem I have is that some people work part time and they are only allowed
to carry forward an equivalent of two days.

I currently have a field on it where people insert their number of hours which
then formats formulas throughout the sheet. I would like it that this would
still be the case.

I hope this makes sense.

Regards

Colin

 

[GS]

Colin Weir presented the following explanation :

Hi There

I developed a time sheet for my organisation using Excel recently. Since
doing this there has been a policy change that I'd like to incorporate into
the time sheet. We work on a Flexi system where we can build up hours to
take time off later. We would like it changed to where after a four week
period if someone has more than a certain amount of hourse that they would
loose the excess.

Basically I work full time so I can carry 21 hours, however if I have 23
hours at the end of a four week period it would automatically reduce to 21
hours.

The problem I have is that some people work part time and they are only
allowed to carry forward an equivalent of two days.

I currently have a field on it where people insert their number of hours
which then formats formulas throughout the sheet. I would like it that this
would still be the case.

I hope this makes sense.

Regards

Colin

Please take no offense!

I have a 'BankedHours' project that does similar for workers who take
lieu days for excess hours worked, OR get a draw for days no work was
available. It does, however, comply with labor laws and so will not
"rip off" workers for any amount of hours, AND includes calcs if a
workweek runs into overtime hours appropriate to weekends/holidays.

Unfortunately, this won't work for the scenario you cite here BUT if
your company ever wants to do it right I'd be happy to disclose details
for a labor law compliant solution.