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??
JAF
Posted by Dave Hawley on April 25, 2001 4:10 AM
Hi JAF
I haven't given this a real test but hopefully it will get you there:
Let's say your dates are in Columns A and B
Try: =TEXT(A1,"mmm")& " = " &EOMONTH(A1,0)-A1& ", " & TEXT(B1,"mmm") & " = " &B1-EOMONTH(B1,-1)
Dave
OzGrid Business Applications
Posted by Dave Hawley on April 25, 2001 4:16 AM
Ok, this seems to work!
=IF(MONTH(B1)<>MONTH(A1),TEXT(A1,"mmm")&" = "&EOMONTH(A1,0)-A1&", "&TEXT(B1,"mmm")&" = "&B1-EOMONTH(B1,-1),TEXT(A1,"mmm")&" = "&B1-A1)
Dave
OzGrid Business Applications
Posted by JAF on April 25, 2001 6:14 AM
Almost, but not quite...
Dave
Thanks again for your help - where would the Excel community be without you and others who offer their guidance.
OK - enough of the grovelling....
Your formula generates a text string stating how may days there are in each month (with one exception that I'll come to in a moment).
Assuming dates are in Columns A and B, what I need is a table with month names Jan to Dec in Column D and for Column E to have the totals for each month as follows:
Month.....Days Absence
Jan 2001..12
Feb 2001..24
Mar 2001..18
etc.
Your formula returns the correct values where there is only one month or 2 months involved, but in the example I gave, one person was abesnt from late february until early April. Your formula only returns the February and April results, not the March result.
Back over to you!!
Posted by Mark W. on April 25, 2001 5:16 PM
JAF, assuming that your data+column headers are in
cells A1:B5, try this:
1. Enter "Jan" in cell C1, and drag the fill handle
to cell N1. C1:N1 should now contain...
{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"}
2. Format columns C:N as... 0;;;@
3. Enter the array formula...
{=SUM((TEXT(TREND(TRANSPOSE($A2:$B2),{0;1}*($B2-$A2)+1,ROW(INDIRECT("1:"&$B2-$A2+1))),"mmm")=C$1)+0)}
...into cell C2, and drag the fill handle to cell N2,
and the down to cell N5.
4. Enter =SUM(C2:C5) into cell C6 and drag the fill
handle to cell N6.
Done!!
I've been asked by out Human Resources department to calculate total employee absence by month.
Posted by Tim Francis-Wright on April 25, 2001 10:05 PM
Re: Almost, but not quite...
I tried to get an array formula to do what you
intend, but I couldn't get around using MIN and
MAX, those pesky kids who handle arrays normally!
Anyway, Chip Pearson's site has a workaround
that will do what you need: you'll need a column
for each month, but it looks easy enough to do.
Hope this helps! Dave Thanks again for your help - where would the Excel community be without you and others who offer their guidance. OK - enough of the grovelling.... Your formula generates a text string stating how may days there are in each month (with one exception that I'll come to in a moment). Assuming dates are in Columns A and B, what I need is a table with month names Jan to Dec in Column D and for Column E to have the totals for each month as follows: Month.....Days Absence
Posted by Tim Francis-Wright on April 25, 2001 10:06 PM
This time, WITH the link!
http://www.cpearson.com/excel/distribdates.htm
Posted by Aladin Akyurek on April 26, 2001 10:22 AM
...
Damn satisfying...
Aladin
PS. I'm just targeting the solution... No side-effects intended.
