Consecutive Leave

[SaraW]

Is it possible to write a formula that scans leave dates and locates the first day of leave (Mon - Fri) and the last day of leave considering public holidays and omitting weekends, from a list of dates?

I understand the NETWORKDAYS formula but am unable to figure out the logic to do this.

 

[mohamed ilyas]

hi

check this..

 

[p45cal]

If someone requests one day leave being a Friday, does that result in start and end dates being just that one day Friday, or does it include the following Sat and Sun, so Start Fri, end Sun?
Similarly, if someone requests Mon to Fri, 5 working days, will the result include both the weekend before and the weekend after, that is a total of 9 consecutive days?
edit:
One more - if there's a one-day public holiday say on a Tuesday, and the request is for one day leave on the Wednesday immediately after, does that count as 2 consecutive days? or one?

 

[SaraW]

hi

check this..

This is perfect and seems so simple in the logic. Thank you so much!

 

[SaraW]

If someone requests one day leave being a Friday, does that result in start and end dates being just that one day Friday, or does it include the following Sat and Sun, so Start Fri, end Sun?
Similarly, if someone requests Mon to Fri, 5 working days, will the result include both the weekend before and the weekend after, that is a total of 9 consecutive days?
edit:
One more - if there's a one-day public holiday say on a Tuesday, and the request is for one day leave on the Wednesday immediately after, does that count as 2 consecutive days? or one?

If there was only one day of leave, then the start and end dates match.
If the leave was Monday to Friday and those dates were in the list for the employee, then it would be Monday to Friday
If the leave was immediately after a public holiday (Tuesday) but the public holiday date wasn't in the leave dates for the employee then the leave starts on the Wednesday which is in the list.