PHIL.Pearce84
Board Regular
- Joined
- May 16, 2011
- Messages
- 152
- Office Version
- 365
- Platform
- Windows
Hi There,
Hope someone can help me?
I'm trying to do some work on resource analysis, essentially what I want to do is count the number of working dates between two dates where someone has booked holiday, and display how many of those days fall within another date range, this could have multiple people within a team.
For example:
Person A books the following holiday:
Start Date 22/7/21 End Date 30/7/21
Person B books the following Holiday within the same team:
Start Date 19/7/21 End Date 30/7/21
This would be 17 working days
On another tab I have the following:
Week 1 Start Date 19/7/21 End Date 23/7/21
Week 2 Start Date 26/7/21 End Date 30/7/21
I want to be able to display that the holiday covers a 2 working week range so:
eek 1 Start Date 19/7/21 End Date 23/7/21 = 7 Days
Week 2 Start Date 26/7/21 End Date 30/7/21 = 10Days
I was trying to use the formula below, but cant seem to get it to work as a range on the holiday tab, works find when the holiday range if set to two cells only but not if Im trying to pick up multiple team members by using a range.
=MAX(MIN(D4,'Itrent Data Dump'!$E$2:$E$122)-MAX(C4,'Itrent Data Dump'!$D$2:$D$122)+1,0)
Any help would be greatly appreciated.
Thanks
Hope someone can help me?
I'm trying to do some work on resource analysis, essentially what I want to do is count the number of working dates between two dates where someone has booked holiday, and display how many of those days fall within another date range, this could have multiple people within a team.
For example:
Person A books the following holiday:
Start Date 22/7/21 End Date 30/7/21
Person B books the following Holiday within the same team:
Start Date 19/7/21 End Date 30/7/21
This would be 17 working days
On another tab I have the following:
Week 1 Start Date 19/7/21 End Date 23/7/21
Week 2 Start Date 26/7/21 End Date 30/7/21
I want to be able to display that the holiday covers a 2 working week range so:
eek 1 Start Date 19/7/21 End Date 23/7/21 = 7 Days
Week 2 Start Date 26/7/21 End Date 30/7/21 = 10Days
I was trying to use the formula below, but cant seem to get it to work as a range on the holiday tab, works find when the holiday range if set to two cells only but not if Im trying to pick up multiple team members by using a range.
=MAX(MIN(D4,'Itrent Data Dump'!$E$2:$E$122)-MAX(C4,'Itrent Data Dump'!$D$2:$D$122)+1,0)
Any help would be greatly appreciated.
Thanks