Calculating employee overtime worked (weekly total) (1 Viewer)

[MsLady]

Hello my beautiful worldwide friends
I am trying to calculate employee Overtime hours from their recorded TimeIn and TimeOut if over 7.5 hours. So anyday they work past 7.5 hours should be calculated and totalled at the end of the week. And i can't seem to figure it out, maybe my maths is bad? Maybe it's my query? The section of my report? What could i be doing wrong here, i have spent hours and im getting no where.

I have attached my report.
Can you help me figure out why my daily total shows but the weekly total is not showing?

 

[gemma-the-husky]

I'll have a look, but just to clarify

if an employee is in and out a few times during the day is their total hours the sum of all the time at work, or a different calc. Also what lunch break is deducted. Finally is it all normal daytime hours or is their any night time stuff

PM me if necessary

 

[MsLady]

Hi gemma,
Yes, their total is the sum of time at work i.e. Sum([timeOut]-[timeIn]). Lunchbreak is calcualted from timeIn, timeout (no fields set aside for that). Yes normal: daytime/night doesn't matter, just timeIn and timeOut and anytime past 7.5 hours should be shown in the OT field and weekly total calculated.

Thanks.hope to hear from you

 

[The_Doc_Man]

The SIMPLEST approach is this:

Date/Time fields in Access are actually DOUBLE fields because Access treats the date and time as a number of days and fractions since a reference date. Most modern O/S - even Windows! - take this treatment.

Therefore, the difference between two date/time fields is the elapsed time between them IN DAYS! To get this in any other time units, multiply the difference - which SHOULD be a DOUBLE - by the right conversion factor. For instance, x 24 if you want HOURS and fractions. x1440 if you wanted MINUTES and fractions. x86400 if you wanted SECONDS, etc.

Just remember that if you were working on fractional testing anyway, stay in a REAL format. Date/time is DOUBLE so that is convenient.

An issue that some people have with this concept is that if the date crosses midnight, you might have to "compound" the operation. That is, take the difference between starting time and midnight for one day's worth of work and then the difference between midnight and ending time for the next day's worth of work. I.e. shifts that cross midnight. If this doesn't apply to your case, consider yourself very lucky. And relieved that you didn't have the headache.

For the curious - in this format and given the reference date, a DOUBLE can hold date/time precise to approximately a fraction of a millisecond until sometime after the year 2150 (give or take), at which point the change in order of magnitude of the day number will cause rounding errors that would probably drop accuracy to millisecond-level. You lose more accuracy sometime after 4500 AD, but I don't have the exact date in front of me.

 

[RuralGuy]

Hi M'Lady,
Try this one. I didn't scrutinize the concept, just got the total to print.

 

[MsLady]

Hi M'Lady,
Try this one. I didn't scrutinize the concept, just got the total to print.

aahh Rural Guy, where have you been all my life?
Thank you so much!

Quick Question: Hope it's not silly to ask, why this works and the one i had in there didn't?

=IIf([txtTotalDailyOT]<>0,[txtTotalDailyOT],0)

 

[MsLady]

The SIMPLEST approach is this:

Date/Time fields in Access are actually DOUBLE fields because Access treats the date and time as a number of days and fractions since a reference date. Most modern O/S - even Windows! - take this treatment.

Therefore, the difference between two date/time fields is the elapsed time between them IN DAYS! To get this in any other time units, multiply the difference - which SHOULD be a DOUBLE - by the right conversion factor. For instance, x 24 if you want HOURS and fractions. x1440 if you wanted MINUTES and fractions. x86400 if you wanted SECONDS, etc.

Just remember that if you were working on fractional testing anyway, stay in a REAL format. Date/time is DOUBLE so that is convenient.

An issue that some people have with this concept is that if the date crosses midnight, you might have to "compound" the operation. That is, take the difference between starting time and midnight for one day's worth of work and then the difference between midnight and ending time for the next day's worth of work. I.e. shifts that cross midnight. If this doesn't apply to your case, consider yourself very lucky. And relieved that you didn't have the headache.

For the curious - in this format and given the reference date, a DOUBLE can hold date/time precise to approximately a fraction of a millisecond until sometime after the year 2150 (give or take), at which point the change in order of magnitude of the day number will cause rounding errors that would probably drop accuracy to millisecond-level. You lose more accuracy sometime after 4500 AD, but I don't have the exact date in front of me.

You know anytime i post a question on this forum. Even my tiniest most silly question. I always get a chapter worth of lecture on my tiny issue. I learn alot each time and in each case, i've had to go back and re-evaluate my db with the fresh knowledge. Thanks for the lesson

 

[RuralGuy]

Quick Question: Hope it's not silly to ask, why this works and the one i had in there didn't?

=IIf([txtTotalDailyOT]<>0,[txtTotalDailyOT],0)

The code you are quoting is in a completely new TextBox that keeps a running total.

 

[MsLady]

Oh that's what the "Running Sum" feature is for. I never noticed or used that
Thanks

 

[RuralGuy]

Glad to help. Make it visible so you can watch it.