LinkBack URL
About LinkBacks
Our policy is, "if you are absent consecutive days, it’s considered one (1) incident."
My tracker works for everything else, except it treats each absent as an incident. How can I make it only count 1 of consecutive days.
Our policy is, "if you are absent consecutive days, it’s considered one (1) incident."
My tracker works for everything else, except it treats each absent as an incident. How can I make it only count 1 of consecutive days.
Try
in D6
=SUMPRODUCT(--((FREQUENCY(IF(D$4=$I6:$NI6,IF($I$5:$NI$5>=EDATE($G$1,-6),IF($I$5:$NI$5<=$G$1,COLUMN($I6:$NI6)))),IF(D$4<>$I6:$NI6,IF($I$5:$NI$5>=EDATE($G$1,-6),IF($I$5:$NI$5<=$G$1,COLUMN($I6:$NI6)))))>0)))
Enter with Ctrl+Shift+Enter
copy down
I get a #NUM!
First it said it found a typo and corrected it.
wait*
Why D6?
See attached:
Look at Name2 : 27 Sept onwards
D6 because I tested on UAs
If this applies to all absent types, drag across.
You will GP numbers go from 2 to 1 for Name1 and Name2
Signing off for today (tonight!).
Last edited by JohnTopley; 11-01-2016 at 06:05 PM.
dang sorry, i was looking at my modified form, i inserted three rows and now D6 is D9 so I forgot.
Either way, I put in a test name and put in your formula in "D" next to it and got that error. Its because of the row changes. My bad. let me adjust it and then test it! Thanks
edit**
OK, i'm close but not there yet. Its like it will not count anything more than one absence, regardless of when
Last edited by taylorsm; 11-01-2016 at 06:27 PM.
Did you try JohnTopley's formula in the file post #4? It has to be array entered.
It works at my end.
Dave
1) Paste the following variation of John's array entered formula (simply adjusted to match the new setup) into D9 of the file attached to post #6:2) Press Ctrl, Shift and Enter simultaneously,Formula:
Please Login or Register to view this content.
3) Drag the fill handle down to D15.
Let us know if you have any questions.
Last edited by JeteMc; 11-01-2016 at 08:33 PM.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Your formula was wrong: use the corrected one provide by JeteMc (mine adjusted for row changes).
Thank you!
Would something like this work?What are the differences? I just recognize SUMPRODUCT vs FREQUENCY
Please Login or Register to view this content.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
I see you have marked your DUPLICATE (!) post as solved so please do the same here.
Yeah you referring to the duplicate that had was up for 10 hours before a response, while this one, posted four hours later had a response in 30 minutes. Gotcha. And as you can tell, i mark solved when solved. I was hoping to understand the difference in approaches before marking as solved and because I wasn't 100% if the other formula was correct, but since it was a duplicate i marked it solved because there isn't a way to delete, or at least when asked, no one has been able to tell me.
Thanks
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks