First day of each week in a year excluding holidays

mossdrive

New member
Joined
Apr 24, 2018
Messages
1
Reaction score
0
Points
0
Excel Version(s)
2016
I am looking for some help on creating a formula that will return the first day of each week in a quarter based on the current year and eliminating holidays. Each returned value would be populated in the header of each column. So far I have...

=WORKDAY(EOMONTH(DATE(E3,1,1),-1),1) gives me the first day of the current year where E3 contains the current year

=WORKDAY(EOMONTH(G3,2),-1) gives me the last day of the third month (Q1)
=WORKDAY(EOMONTH($G$3,-1), 1,DATEDATA!$B$10:$B19) gives me the first "workday" after the first day of the year excluding holidays which are contained on a different sheet at $B$10:$B19

This all works how I want it. Where I am stuck is getting the first day of each subsequent week after the first workday excluding the holidays I have defined. I have tried =WORKDAY(EOMONTH($G$3,-1), 5,DATEDATA!$B$10:$B19) incrementing the number of days by 5 but this doesn't work as it doesn't account for any missing days this way due to holidays. glad of any assistance
icon_smile.gif

Thanks in advance!
 
Let me see if I understand...
If we are talking 2018, then the first working day is Tuesday, 2nd January (assuming that New Years Day is a holiday). In the next week (assuming no further holidays) it would be Monday, 8th January ?
 
Last edited:
In the absence of further input from the OP I've taken a stab at a solution that should be adaptable if it isn't quite right. Its a good example of the flexibility/adaptability of certain date/time functions when combined with an array formula.
 

Attachments

  • Dates11.xlsx
    12.2 KB · Views: 18
Back
Top