Count consecutive days of absence
Hello everyone
I am trying to create a formula that will count the days of absence for each person, but only the days of continuous absence.
In the first column :person number,
2nd column: starting day ,
3rd column: end day
I've tried to do this with formulas, but i was able to find max/min date, but the point is to count consecutive days. I
maybe it's posibble to do this in power query ?
I will be grateful for any tips.
in attachment sample data.
Best regards,
Mark
PS. Merry Christmas
What about
D2=[@[absent to]]-[@[absent from]]+1
Then you could use a Pivot Table to make the complete sum
Last edited by PCI; 12-26-2021 at 12:08 PM.
- Battle without fear gives no glory - Just try
No, 'cos 10031 should be 4 and 12 (two occasions) not 16.
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
Pl see file.
In F2 then copied down
If last date of continuous absence is required, in H2 then copied down
=IFERROR(IF($B2<>SUM($C1)+1,IF($C2<>$B3-1,$C2-$B2+1,IF($A3=$A2,AGGREGATE(14,6, ($C$3:$C$55)/(($B$3:$B$55=($C$2:$C$54+1))*($A$3:$A$55=$A2)),1)-$B2+1)),""),"")
=IFERROR(IF($B2<>SUM($C1)+1,IF($C2<>$B3-1,$C2,IF($A3=$A2,AGGREGATE(14,6, ($C$3:$C$55)/(($B$3:$B$55=($C$2:$C$54+1))*($A$3:$A$55=$A2)),1))),""),"")
Last edited by kvsrinivasamurthy; 12-26-2021 at 01:45 PM.
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
This is brilliant but been trying to work out if the employee is still on sick leave(no end date)so I need a continuous count from the start day to ongoing calendar day,
I hope this makes sense
kvs... that looks like it!! I have been playing with this from time to time all day with no luck.... If it's correct it's lovely.
Thanks for the positive comments.Originally Posted by Glenn Kennedy
I'm looking at it and can't believe my eyes
Absolutely fantastic solution.
thank you very much Sir.
Best regards,
Mark
@Mark123321
Thanks for the feedback.
Pl mark the thread solved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks