# Calculating completed jobs by shift.

## Recommended Posts

Hi

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

CompletedJobTimestamp

CompletedJobTimestamp_TimeONLY

CompletedJobTimestamp_DateONLY

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.

##### 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
• 1
##### Share on other sites

I  don't understand how it works but it does

Thanks for helping me on this.

Edited by Plucky
##### Share on other sites

29 minutes ago, Plucky said:

I  don't understand how it works

I suggest you spend some time on this, because it will come handy in any calculation involving periodical phenomena such as dates and times.

## Create an account

Register a new account