• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Get start and end dates of the holidays of selected name from attached calendar

smaoketown2021

New Member
I need to get the start and end dates of the holidays for the upcoming 3 months only of the person selected in cell M3 drop down list (Tab “Start end”).
  • Calendar is in tab ‘’Calendar”
  • Start and end dates needed only if person has consecutive 5 days or more away from work. Days away from work can include “va”-vacation,”bh”-Bank holiday and weekends (Saturday/Sunday).
  • “BT”-Business trip, “S”-Sick and “F”- flow is not considered as days away from work.
Please help!
 

Attachments

vletm

Excel Ninja
smaoketown2021
Seems that You've done same ... with two Forums ... missed to follow their Rules.
Below, the link to Chandoo.org's, which has clear sentences with cross-posting.
 

smaoketown2021

New Member
smaoketown2021
Seems that You've done same ... with two Forums ... missed to follow their Rules.
Below, the link to Chandoo.org's, which has clear sentences with cross-posting.
Sorry. im new at this will declare it or avoid cross post the next time. Please help, if possible. Thank you.
 
Last edited:

Peter Bartholomew

Well-Known Member
You didn't state the Excel version to which you have access. I wouldn't touch anything other than 365 for anything beyond the most trivial of problems.
Code:
= LET(
  status,      XLOOKUP(NameSelected, StaffName, Calendar),
  working?,    (status<>"bh")*(status<>"Va")*(WEEKDAY(+Date,11)<6),
  workdays,     FILTER(Date, working?),
  days,         FREQUENCY(Date, workdays)-1,
  k,            1+SEQUENCE(COUNT(days)-1),
  daysLeave,    INDEX(days, k),
  holidayStart, 1+FILTER(workdays, daysLeave>4),
  duration,     FILTER(daysLeave, daysLeave>4),
  holidayEnd,   holidayStart + duration -1,
  IF({1,0}, holidayStart, holidayEnd) )
The 'upcoming 3 months still needs to be implemented but should be trivial.
Have you got results from the other forum/s?
 

Attachments

smaoketown2021

New Member
You didn't state the Excel version to which you have access. I wouldn't touch anything other than 365 for anything beyond the most trivial of problems.
Code:
= LET(
  status,      XLOOKUP(NameSelected, StaffName, Calendar),
  working?,    (status<>"bh")*(status<>"Va")*(WEEKDAY(+Date,11)<6),
  workdays,     FILTER(Date, working?),
  days,         FREQUENCY(Date, workdays)-1,
  k,            1+SEQUENCE(COUNT(days)-1),
  daysLeave,    INDEX(days, k),
  holidayStart, 1+FILTER(workdays, daysLeave>4),
  duration,     FILTER(daysLeave, daysLeave>4),
  holidayEnd,   holidayStart + duration -1,
  IF({1,0}, holidayStart, holidayEnd) )
The 'upcoming 3 months still needs to be implemented but should be trivial.
Have you got results from the other forum/s?
Yes I use 365.
This works great. Thank you. After using it I see that I don't really need to restrict to upcoming 3 months since people don't usually plan holidays too far ahead. I was worried there would be too much data making it hard to read for the user but it looks quite nice. I will mark the post as Solved.

Thank you again.
 
Top