Calculate absence by month
Posted by JAF on April 25, 2001 3:44 AM
I've been asked by out Human Resources department to calculate total employee absence by month.
They hold the First Day Absent and Last Day Absent fields (dd/mm/yyyy date format).
When the absence falls within a single month, it's easy to calculate, the problem I have is that absence can fall over 2 (or more) months as follows:
First Day....Last Day
20/02/2001...21/02/2001 (February 2 days)
26/02/2001...05/03/2001 (February 3 days, March 5 days)
27/02/2001...09/04/2001 (February 2 days, March 31 days, April 9 days)
06/03/2001...09/03/2001 (March 4 days)
Totals for the above example are:
February: 7 days
March: 40 days
April: 9 days
I can't work out any way that will give me the total number fo days absence by month. Any suggestions??