Deducting meal breaks

[astrom33]

Hi everyone,

I have a table with employee names, time in, time out, and total hours.

In a query I want to create an expression (i.e., Total Hours After Meal Break) that will deduct a 30 minute meal break from any day where the total hours column is greater than or equal to 6.5, but less than or equal to13. I also need to deduct 1 hour for a meal break where the total hours column is greater than 13. However, if the total hours is less than 6.5 hours, then no need to the hours needs to be made.

So far I have this expression:

[FONT=&quot]Total Hours After Meal Break: IIf([Total Hours]<=13 And [Total Hours]>=6.5,[Total Hours ]-0.5,[Total Hours]-1)[/FONT]

The expression works insofar as it is deducting either 30 minutes where the worker works more than 6.5 but less than 13 hours. However, for days where the worker is working less than 6.5 hours it is also deducting 1 hour. Again, on those days where the worker has less than 6.5 hours, no change needs to be made.

Any help is greatly appreciated.

 

[plog]

Try this:

Total Hours After Meal Break: [TotalHours]-IIf([Total Hours]>=6.5, .5, 0) - IIf([Total Hours]>=13, .5, 0)

 

[spikepl]

Try

[TotalHours]-Int([TotalHours]/6.5)*0.5

You may have to play with some rounding

 

[astrom33]

Try

[TotalHours]-Int([TotalHours]/6.5)*0.5

You may have to play with some rounding

Thanks!! That worked very well. However, on the few instances that the person worked more than 19.5 hours I would want it to deduct 1.5 hours. In other words an extra 30 minutes should be deducted for every 6.5 hours worked.

Any ideas?

 

[spikepl]

Yes. Plug in 19.6 and see what the formula gives.

 

[astrom33]

Yes. Plug in 19.6 and see what the formula gives.

I was doing the math wrong in my head. The output worked.

You are 100% correct. The expression you gave me worked like a charm. Thank you VERY much.