Jump to content

Calculating completed jobs by shift.

Recommended Posts



I’m trying to calculate how many jobs employees complete on a day sorted by shift.


We have 3 Shifts:

Early Shift - 06:30:01 to 14:30:00

Evening Shift - 14:30:01 to 22:30:00

Late Shift - 22:30:01 to 06:30:00


I have 3 fields





I'm not sure if I should be converting "CompletedJobTimestamp_TimeONLY" to seconds and running a if statement to see if it falls into 1 of 3 ranges to give it an Early, Evening or Late result.

Then I can sort using this result under a date search. The problem I have then is the late shift runs into the next day's morning. 

 Any ideas on the best way to approach this.

Link to comment
Share on other sites

If you only want to group the records by shift, you could calculate some value that will be common to all records within the same shift - a sort of an "absolute shift number", if you like:

Div ( CompletedJobTimestamp - Time ( 6 ; 30 ; 0 ) ; Time ( 8 ; 0 ; 0 ) )

If you also need a label, you can calculate it from the above using:

Choose ( Mod ( cShiftNum ; 3 ) ; "Early" ; "Evening" ; "Late" )

Similarly, the start date of the shift can be calculated as:

Div ( cShiftNum ; 3 ) + 1

Note that this calculation assumes that a shift starts exactly at a round half-hour - i.e. a record with timestamp ending in 06:30:00 is allocated to the Early shift, while 06:29:59.999 is counted in the Late shift.


Edited by comment
  • Like 1
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...

Important Information

By using this site, you agree to our Terms of Use.