I'm hoping someone can help me out with a calculation that is way beyond my ability. I'm only self taught on FileMaker (databases in general) since last October. The calculation I need done seems pretty tricky to me.
I'm working on a sick report db that will automatically calculate the number of working days someone has been absent due to being sick or injured. Our work schedule is five days on followed by three days off, repeat. Our work force is broken down into nine different squads; 1,2,3,4,5,6,7,8 and Admin, each representing a different schedule. For example:
Squad 1 will work Jan 1,2,3,4,5 off Jan 6,7,8 back on Jan 9,10,11,12,13 off three more etc.
Squad 2 will work Jan 2,3,4,5,6 off Jan 7,8,9 back on Jan 10,11,12,13,14 off three more.
Squad 3 starts Jan 3 etc..I hope you can get the idea now.
Admin works Monday thru Friday with every other Friday off.
So if someone books off sick on Feb 11 2012 in squad 3 and returns to work on Apr 6 2012 still in squad 3 I would like it to calculate how many working days they have missed.
I already have fields for "date_off", "squad_off", "date_return" "squad_return". Besides the field to do this calculation is there anything else I'll need. Any help with this would be greatly appreciated.