A1= Name
B1= Date
I am tracking attendance and one of the rules in the policy is consecutive days absent counts as 1 occurrence.
Column A Column B
Dave Jan. 2, 2016
Dave Jan. 3, 2016
Dave April 1, 2016
Dave April 5, 2016
I would like the count of occurrences, from this example it would be 3 since Jan. 2 and Jan. 3 are counted as 1.
I suspect that this is more complicated than you are currently stating.
What is the effect of weekends and public holidays? Will Fred's absences be in the same column, all mixed up with Dave's?
Please attach a sample workbook. Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!
However, nothing on this Forum works quite as well as you might expect. The attachment icon doesn't work!! (Helpful, isn't it!!). Instead, just before you submit your post, click on GO ADVANCED (near the bottom) and then scroll down to Manage Attachments to open the upload window. The relevant instructions are at the top of that screen.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
This solutions employs a helper column (C) which may be hidden for aesthetic purposes. The formula to populate the helper column is:The formula that will count the number of absence occurrences is:Formula:
=IF(B1=B2-1,1,0)Let me know if you have any questions.Formula:=COUNT(B2:B5)-SUM(C2:C5)
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
@Glenn,
I was able to attach my file and you are correct, this is slightly more complicated.
I added more data and my intended results in columns D and E.
if there's a way to include holidays and weekends that would be ideal. For example, an occurrence on Friday and another occurrence on Monday still equals 1 (assuming this employee has Saturday and Sunday off days)
For company holidays, I could probably enter those on my list for every employee if it's not possible creating a formula for it.
I modified the formula that counts the number of absences to take into consideration of the employees name:I assume from your comment that some employees work weekends, as in Dave and Steve, which would complicate the blanket inclusion of the workday function.Formula:=COUNTIFS(A$2:A$13,D2)-SUMIFS(C$2:C$13,A$2:A$13,D2)
Let me know if you have any questions.
@jetemc, when I enter the formula I get large negative numbers. Can you check the formula again?
was able to make the formula work, but this formula does not combine consecutive days.
Here is the file with the formulas applied.
Let me know if you have any questions.
I think the issue with this formula is I have to have names and dates sorted correctly all the time. For example, I will most likely have multiple employees calling off sick on the same day, and the formula in column D doesn't take that into account.
One possible 'fix' might be to create an Excel table that could be sorted either by date to enter new data about absences, or by name to display the correct number of absence occurrences per employee.
Let me know if you have any questions.
Hi,
I propose an adaptation of the JeteMc`s solution, that takes into account holidays and weekends
for helper column (C) use this
Formula:=IFERROR(IF(NETWORKDAYS(B1,B2,$H$2:$H$3)>2,0,-1),0)
where H2: H3 is the list of holidays
For List of unique names
use this array formula (in E2 and drag down)
Formula:=IFERROR(INDEX($A$2:$A$13,MATCH(0,COUNTIF($E$1:E1,$A$2:$A$13),0)),"")
and for "Count of occurrences" column
Formula:=IF(E2="","",SUMIF($A$2:$A$13,E2;$C$2:$C$13)+COUNTIF($A$2:$A$13,E2))
see attached
Another way.
Helper formula in column C.In column EFormula:=--IF(A2=$A$2:$A$16,OR(AND(NETWORKDAYS.INTL(N($B1),$B2,"0000000")<>0,NETWORKDAYS.INTL(N($B1),$B2,"0000000")<>2),COUNTIF($A$2:A2,A2)=1))Formula:=SUMIF(A:A,D2,C:C)
I put in some more dates (contiguous across weekends). It seems to work at my end.
Dave
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Sasquatch2014 
Register To Reply
Bookmarks