• 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

S

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

  • Data.xlsx
    80 KB · Views: 11
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.

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...
chandoo.org
 
vletm said:
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.

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...
chandoo.org
Click to expand...
Sorry. im new at this will declare it or avoid cross post the next time. Please help, if possible. Thank you.
 
Last edited:
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

  • workCalendar.xlsx
    80.6 KB · Views: 13
Peter Bartholomew said:
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?
Click to expand...

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.
 
Peter Bartholomew said:
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?
Click to expand...
Hi Peter, thanks for the solution. Following up on this question, what if every holiday start and end date needs to be captured and displayed regardless of the length, in other words, not only showing 5 days or longer holidays but also covering1 day leave? how should the formula be modified? Looking forward to your comments.
 
As it stands, the formula will detect shorter leave periods but single days get tangled up with weekends.
The attached version allows you to explore the working of the formula by stepping through it and also changing the filter levels.
 

Attachments

  • workCalendar (1).xlsx
    104.5 KB · Views: 10
Peter Bartholomew said:
As it stands, the formula will detect shorter leave periods but single days get tangled up with weekends.
The attached version allows you to explore the working of the formula by stepping through it and also changing the filter levels.
Click to expand...
Hi Peter, Thanks for the breakdown of formula and sorry for my late response, I've been sick and then tied up with work.
I've studied the file and noticed in the current formula logic, n must >2, so if we want to show the planned 1 day leave as well, we need to re-write the formula o_O
Is it possible to add this special condition on top of the existing formula, ie, if there is one or two "Va" within the weekdays, it can be displayed?
I am just thinking of it and it may not be workable, please let me know your thoughts, any comments is appreciated.
Many thanks in advance.
 
You must log in or register to reply here.
Back
Top