Jump to content

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.

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

Posted (edited)

I  don't understand how it works but it does

Thanks for helping me on this.

Edited by Plucky
Link to comment
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.

 

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
 Share

×
×
  • Create New...

Important Information

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