Calculate Overlapping Leave for different categories
Hi,
Could you please help me with a formula.
I need to calculate overlapping leave on each day for particular categories.
I have attached sample data.
For eg; In sample data, there are 2 teachers on leave on 1 July, so I need to get 2 under 1 July in the sheet.
Thanks.
Hi, attached solution using Power Query, Since you are using Excel 2016, just bring in your table to Power Query Editor, sort ascending Start date
End Date, create a custom column to find out the dates in between each category Start and end date
Group it, aggregate then pivot it, this is the first query same as your sheet1
then aggregate (Sum) by category in 2nd query to obtain the second summary table that you want
Please go to Power Query editor to reference my solution
Rgds
Christopher Yap
Hi,
In I2 and across you can use the below ARRAY formula:
=COUNT(IF(($G2=$E$11:$E$15)*($C$11:$C$15 <=I$1)*($D$11:$D$15 >=I$1),ROW($B$11:$B$15)))
*array formulas are confirmed with CTRL+SHIFT+ENTER
You can also use the countifs function, but for some reason I wasn't able to get this work....
=COUNTIFS($E$11:$E$16,$G2,$C$11:$C$16,"<=I$1",$D$11:$D$16,"=>I$1")
Thanks Belinda, appreciate that.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks