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.

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 June 10, 20214 yr by comment

I  don't understand how it works but it does

Thanks for helping me on this.

Edited June 10, 20214 yr by Plucky

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.