Logically will a person take a sick day when not scheduled to work? my guess is no.
In recording the dates a person is out sick where will that field be? what table? my guess is attendance table
I see four tables to do this: 1) the person table - employee (with their personal info) 2) the attendance table (sickday date goes here) 3) the group table and 3) the join table ( tells what person is in what group)
If you want to see sick absences in a report and the sickdayout field is in the attendance table, base the report on the attendance table, find all records falling inside your report period and add summary field to count the records in the report.
Person =< attendance
Group =< join table >= person
Hope this Helps,
While a lot would depend on how strict you want to be on your first day off (can it be a non-work day etc), your calculation would basically take the form of:
Date of interest (first sick day etc) - Date of reference (first day of the first cycle for the group) + group (1-9)
With this basic date calculation above, you can use the DIV and MOD functions to get the PERIOD of the date (the nth ineration of the 8 day cycle) and the day within that cycle
DIV (firstSickDay - DateOfReference + group ; 8) // the cycle of the date
MOD (firstSickDay - DateOfReference + group ; 8) // the day within the cycle
From here you would need to decide if the MOD caclculation was >5 (an off day) etc if you desired to make it more robust
Now you can use the difference between the cycle numbers for the two dates and the difference between the days within the cycle for the two dates to handle the actual sick days.
1 of 1 people found this helpful
I have 2 questions.
1. I understand how the Squads rotations work - an 8 day period, but the Admin people seem to be on a 14 day rotation - work 5, off 2, work 4, off 3, repeat. Is that correct?
2. Since you have 2 parameters denoting Squad (squad_off and squad_return), are we to assume that while a person is out sick, their squad can change - hence their work and off days change? How can we know when (what date) they switch squads if the only 2 dates are date_off and date_return?
I'll post a solution (custom function) next.
Here's a custom function that computes the Missed Work Days for Squads 1-8 or Admin (Squad 0) from date_off to date_return. I'll post the CF here and include a link to a demo file. Squad Missed Work Days.fp7
// MissedWorkDays( date_off ; date_return ; squad )
// squad = 0 is for Admin;
// squad = 1 to 8 are for squads.
IsValid( GetAsDate( date_off ) ) and not IsEmpty( date_off ) and
IsValid( GetAsDate( date_return ) ) and not IsEmpty( date_return ) and
IsValid( GetAsNumber( squad ) ) and not IsEmpty( squad ) and GetAsNumber( squad ) >= 0 and GetAsNumber( squad ) <= 8 ;
sd = GetAsDate( date_off ) ;
ed = GetAsDate( date_return ) ;
squadOffset = GetAsNumber( squad ) - 1 ;
baseDate = Date( 1 ; 1 ; 2012 ) ;
workArray = Case( squadOffset < 0 ; "01111100111100" ; "11111000" ) ; // Distinguish between Squads and Admin
workDaysInPeriod = PatternCount( workArray ; "1" ) ;
lenWorkArray = Length( workArray ) ;
deltaDays = ed - sd ;
wholePeriods = Div( deltaDays ; lenWorkArray ) ;
partialPeriod = Mod( deltaDays ; lenWorkArray ) ;
leftShift = Mod( sd - baseDate ; lenWorkArray ) ;
crewArray = Case( squadOffset < 0 ; "01111100111100" ; Right( workArray ; squadOffset ) & Left( workArray ; lenWorkArray - squadOffset ) ) ;
partialCrewArray = Left( Right( crewArray ; lenWorkArray - leftShift ) & Left( crewArray ; leftShift ) ; partialPeriod )
workDaysInPeriod * wholePeriods + PatternCount( partialCrewArray ; "1" )
Thanks for the responses guys, sorry for the late reply, away for the weekend.
To answer your first question Jason you are correct about the Admin schedule, 5 on 2 off, 4 on 3 off, repeat. The reason for the squad_return is because it is possible for someone to return and be assigned to a different squad for various reason ( length of absence, time of year assignment changes).
To answer the question about people booking off, someone would only be booking off of work on a work day. And it needs to be accurate because once someone is absent for 20 consecutive days they get transferred.
I really appreciate the help, I go back to work tomorrow and will try to implement this, bear with me though, since I don't have a lot of experience I'll probably need to ask a few more questions to get this going. Thanks again.
1 of 1 people found this helpful
First of all, the Custom Function I wrote does not care if the "date_off" or "date_return" are workdays or off-days; it will correctly count the missed workdays from the "date_off" up to but not including the "date_return" (because if they returned on that date, they weren't 'sick' that day).
As I said before, the "Admin" Squad is Squad 0 (zero) in the Custom Function - although the "squad" parameter in the custom function could be changed to a text-type field and handle "Admin" and Squad numbers "1", "2", ... , "8" as valid inputs.
To further explain my question regarding the "squad_return" field. If someone gets sick while on one squad (squad_off) and is reassigned to another squad (squad_return) while hi is sick, we would need to know the date (date_switched) on which he was re-assigned to the new squad in order to account for the missed work days for each squad. However, 2 calls to the Custom Function, one with "date_off", "date_switched", "squad_off" and the other with "date_switched", "date_return", "squad_return" would give the correct total of missed work days. On the other hand, if the person isn't re-assigned to the new squad until his return, then only 1 CF call is needed and the "squad_return" is not needed by the CF.
Fortunately, a squad change wouldn't occur until someone came back to work. I'll let you know how I make out with this tomorrow.
Works like a charm! I just had to adjust the baseDate to sync up with our work calender (Jan 1st wasn't really squad 1's first day) and all is well, thank you very much.
Good to hear. Just make sure the baseDate also syncs with the 1st day of the Admin folks as well - or adjust the "01111100111100" Admin array to start on the base Date you are using.