Setting up Observed Holidays on a Dynamic Calendar based upon the year

life_in_picture_format

life_in_picture_format

New Member
Joined
Dec 8, 2021
Messages
26
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Ok, I am very sorry but I cannot get the XL2BB tool to work for me these days. Excel keeps blocking it and after a few tries following the "how-to" I've decided to post a screenshot with my question.
I am trying to make my mom this calendar/vacation accrual calculator worksheet. If someone uses 8 hours of VAC or whatever other types of "Leave" sh will input the number of hours on this sheet under the correct month/date and I've set up the formulas to add the accruals and subtract the time used (that's the easy part). Here is a screenshot:

1651609571501.png

This is set to adjust the dates based upon the year which she types up in the year cell in the left corner.

Formula for the days to adjust are in the rows of dates, for example, Feb (row 42) the first Monday is a blank cell here is the formula: =IF(WEEKDAY(A42,1)=$I$4, A42,"")
For the following day Feb 1st under Tuesday formula is: =IF(B42="",IF(WEEKDAY(A42,1)=MOD($I$4,7)+1,A42,""),B42+1)
I there a way to maybe use conditional formatting to highlight the Holidays for the year based upon that specific year. See here if a holiday lands on a Sunday we observe it on that following Monday or if a holiday falls on a Saturday it is observed on Friday before. They are paid holiday pay so she would like to track that but I am not sure how to set that up for her.
Wanted to bring this one to you all to see what you think.
IF you need more info let me know and I will try to post the XL2BBB
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
I'm not sure how to wire this idea into your workbook, but you could include rows under each month (I'm assuming the months continue down the sheet?) to flag holidays and then use conditional formattting to highlight a cell or range of cells based on that. These rows could be hidden from view then.
Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAM
9MTWTFSSMTWTFSSMTWTFSSMTWTFSSMTWTFSSMT
10Jan-2212345678910111213141516171819202122232425262728293031
11           H                H        Holidays
12
13
14Feb-2212345678910111213141516171819202122232425262728
15               H                     Holidays
Sheet2
Cell Formulas
RangeFormula
B11:AL11,B15:AL15B11=IF(ISNUMBER(MATCH(DATE(YEAR($A10),MONTH($A10),DAY(B10)),Sheet3!$C$3:$C$5,0)),"H","")

The made-up holidays are on another sheet and referenced by the MATCH formula. I suppose the "Celebrated on" date could use a formula to determine whether the Friday before or Monday after applies.
Book1
ABC
1Holidays
2NameDateCelebrated
3ADay1/7/20221/7/2022
4Bday1/23/20221/24/2022
5Cday2/15/20222/15/2022
Sheet3

Let me know if you need any more detail.
 
Upvote 0
I got the XL2BB to work! YES! Does this take in account ONLY weekends @KRice? If like Christmas falls on a Saturday I want it to highlight the Friday before as the Observed USA Holiday or if it falls on a Sunday I want it to highlight Monday as the observed Holiday without having toeven think about it



moms test cal.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKAL
1Accruals
2
3YearMonthStart Day
42021121: Sunday, 2: Monday
5VacationPersonal Holiday
6NAME:EE:Accrual Level:7Accrual Level:C
72021
8[42]
9MTWTFSSMTWTFSSMTWTFSSMTWTFSSMTWTFSSMT
10January 2021    12345678910111213141516171819202122232425262728293031  
11Personal Holiday1
12Vacation
13Sick Leave
14Family Sick Leave2
15Admin Leave4
16Bereavement
17Vacation in Lieu of Sick
18FMLA
19PDL
20OPT
21OVT
YearlyCalendar
Cell Formulas
RangeFormula
A7A7=IF($C$4=1,A4,A4&"-"&A4+1)
B9B9=CHOOSE(1+MOD($I$4+1-2,7),"S","M","T","W","T","F","S")
C9C9=CHOOSE(1+MOD($I$4+2-2,7),"S","M","T","W","T","F","S")
D9D9=CHOOSE(1+MOD($I$4+3-2,7),"S","M","T","W","T","F","S")
E9E9=CHOOSE(1+MOD($I$4+4-2,7),"S","M","T","W","T","F","S")
F9F9=CHOOSE(1+MOD($I$4+5-2,7),"S","M","T","W","T","F","S")
G9G9=CHOOSE(1+MOD($I$4+6-2,7),"S","M","T","W","T","F","S")
H9H9=CHOOSE(1+MOD($I$4+7-2,7),"S","M","T","W","T","F","S")
I9:AL9I9=B9
A10A10=DATE($A$4,$C$4,1)
B10B10=IF(WEEKDAY(A10,1)=$I$4,A10,"")
C10C10=IF(B10="",IF(WEEKDAY(A10,1)=MOD($I$4,7)+1,A10,""),B10+1)
D10D10=IF(C10="",IF(WEEKDAY(A10,1)=MOD($I$4+1,7)+1,A10,""),C10+1)
E10E10=IF(D10="",IF(WEEKDAY(A10,1)=MOD($I$4+2,7)+1,A10,""),D10+1)
F10F10=IF(E10="",IF(WEEKDAY(A10,1)=MOD($I$4+3,7)+1,A10,""),E10+1)
G10G10=IF(F10="",IF(WEEKDAY(A10,1)=MOD($I$4+4,7)+1,A10,""),F10+1)
H10H10=IF(G10="",IF(WEEKDAY(A10,1)=MOD($I$4+5,7)+1,A10,""),G10+1)
I10:AL10I10=IF(H10="","",IF(MONTH(H10+1)<>MONTH(H10),"",H10+1))
Named Ranges
NameRefers ToCells
January=YearlyCalendar!$A$10B10:H10
Saturday1=YearlyCalendar!$G$9:$G$392N9
Saturday2=YearlyCalendar!$N$9:$N$392U9
Saturday3=YearlyCalendar!$U$9:$U$392AB9
Saturday4=YearlyCalendar!$AB$9:$AB$392AI9
Sunday1=YearlyCalendar!$H$9:$H$392O9
Sunday2=YearlyCalendar!$O$9:$O$392V9
Sunday3=YearlyCalendar!$V$9:$V$392AC9
Sunday4=YearlyCalendar!$AC$9:$AC$392AJ9
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F11:F41Expression=$F$10=1textNO
B362:F362,B202:F202,B234:F234,B266:F266,B298:F298,B330:F330,B74:M74,B106:M106,B138:M138,B170:M170,I330:M330,I298:M298,I266:M266,I234:M234,I202:M202,I362:M362,G202:H362,P362:T362,P202:T202,P234:T234,P266:T266,P298:T298,P330:T330,P170:T170,P138:T138,P106:T106Cell Value=""textYES
Cells with Data Validation
CellAllowCriteria
AG6:AI6ListM, C
Y6:AA6List=TIMEOFF_LEAVE!$K$4:$K$13

KRice said:
I'm not sure how to wire this idea into your workbook, but you could include rows under each month (I'm assuming the months continue down the sheet?) to flag holidays and then use conditional formattting to highlight a cell or range of cells based on that. These rows could be hidden from view then.
Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAM
9MTWTFSSMTWTFSSMTWTFSSMTWTFSSMTWTFSSMT
10Jan-2212345678910111213141516171819202122232425262728293031
11           H                H        Holidays
12
13
14Feb-2212345678910111213141516171819202122232425262728
15               H                     Holidays
Sheet2
Cell Formulas
RangeFormula
B11:AL11,B15:AL15B11=IF(ISNUMBER(MATCH(DATE(YEAR($A10),MONTH($A10),DAY(B10)),Sheet3!$C$3:$C$5,0)),"H","")

The made-up holidays are on another sheet and referenced by the MATCH formula. I suppose the "Celebrated on" date could use a formula to determine whether the Friday before or Monday after applies.
Book1
ABC
1Holidays
2NameDateCelebrated
3ADay1/7/20221/7/2022
4Bday1/23/20221/24/2022
5Cday2/15/20222/15/2022
Sheet3

Let me know if you need any more detail.
Click to expand...
 
Upvote 0
moms test cal.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKAL
9MTWTFSSMTWTFSSMTWTFSSMTWTFSSMTWTFSSMT
10January 2021    12345678910111213141516171819202122232425262728293031  
11Personal Holiday1
12Vacation
13Sick Leave
14Family Sick Leave2
YearlyCalendar
Cell Formulas
RangeFormula
B9B9=CHOOSE(1+MOD($I$4+1-2,7),"S","M","T","W","T","F","S")
C9C9=CHOOSE(1+MOD($I$4+2-2,7),"S","M","T","W","T","F","S")
D9D9=CHOOSE(1+MOD($I$4+3-2,7),"S","M","T","W","T","F","S")
E9E9=CHOOSE(1+MOD($I$4+4-2,7),"S","M","T","W","T","F","S")
F9F9=CHOOSE(1+MOD($I$4+5-2,7),"S","M","T","W","T","F","S")
G9G9=CHOOSE(1+MOD($I$4+6-2,7),"S","M","T","W","T","F","S")
H9H9=CHOOSE(1+MOD($I$4+7-2,7),"S","M","T","W","T","F","S")
I9:AL9I9=B9
A10A10=DATE($A$4,$C$4,1)
B10B10=IF(WEEKDAY(A10,1)=$I$4,A10,"")
C10C10=IF(B10="",IF(WEEKDAY(A10,1)=MOD($I$4,7)+1,A10,""),B10+1)
D10D10=IF(C10="",IF(WEEKDAY(A10,1)=MOD($I$4+1,7)+1,A10,""),C10+1)
E10E10=IF(D10="",IF(WEEKDAY(A10,1)=MOD($I$4+2,7)+1,A10,""),D10+1)
F10F10=IF(E10="",IF(WEEKDAY(A10,1)=MOD($I$4+3,7)+1,A10,""),E10+1)
G10G10=IF(F10="",IF(WEEKDAY(A10,1)=MOD($I$4+4,7)+1,A10,""),F10+1)
H10H10=IF(G10="",IF(WEEKDAY(A10,1)=MOD($I$4+5,7)+1,A10,""),G10+1)
I10:AL10I10=IF(H10="","",IF(MONTH(H10+1)<>MONTH(H10),"",H10+1))
Named Ranges
NameRefers ToCells
January=YearlyCalendar!$A$10B10:H10
Saturday1=YearlyCalendar!$G$9:$G$392N9
Saturday2=YearlyCalendar!$N$9:$N$392U9
Saturday3=YearlyCalendar!$U$9:$U$392AB9
Saturday4=YearlyCalendar!$AB$9:$AB$392AI9
Sunday1=YearlyCalendar!$H$9:$H$392O9
Sunday2=YearlyCalendar!$O$9:$O$392V9
Sunday3=YearlyCalendar!$V$9:$V$392AC9
Sunday4=YearlyCalendar!$AC$9:$AC$392AJ9
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F11:F41Expression=$F$10=1textNO
B362:F362,B202:F202,B234:F234,B266:F266,B298:F298,B330:F330,B74:M74,B106:M106,B138:M138,B170:M170,I330:M330,I298:M298,I266:M266,I234:M234,I202:M202,I362:M362,G202:H362,P362:T362,P202:T202,P234:T234,P266:T266,P298:T298,P330:T330,P170:T170,P138:T138,P106:T106Cell Value=""textYES
 
Upvote 0
Sure, you'll get this result:
MrExcel_20220503.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAM
9MTWTFSSMTWTFSSMTWTFSSMTWTFSSMTWTFSSMT
10Jan-2212345678910111213141516171819202122232425262728293031
11           H                H        Holidays
12
13
14Feb-2212345678910111213141516171819202122232425262728
15               H  H         H        Holidays
Sheet2
Cell Formulas
RangeFormula
B11:AL11,B15:AL15B11=IF(ISNUMBER(MATCH(DATE(YEAR($A10),MONTH($A10),DAY(B10)),Sheet3!$C$3:$C$10,0)),"H","")

And then a formula on the holiday sheet will handle the Friday/Monday rule. This checks whether the manually inputted holiday dates (in blue) fall on a Sat or Sun, and then either subtracts or adds one day to arrive at the "celebrated on" date:
MrExcel_20220503.xlsx
ABC
1Holidays
2NameDateCelebrated
3Aday1/7/20221/7/2022
4Bday1/23/20221/24/2022
5Cday2/15/20222/15/2022
6Dday2/19/20222/18/2022
7Eday2/27/20222/28/2022
8
9
10
Sheet3
Cell Formulas
RangeFormula
C3:C7C3=IF(WEEKDAY(B3,2)=6,B3-1,IF(WEEKDAY(B3,2)=7,B3+1,B3))
 
Upvote 0
Solution
Where do you want the highlighting to appear for holidays, and what would the highlighting look like? Are the cells a certain color...and is it only the day number that is highlighted, or do you want a range of cells extending down the column highlighted on those days?
 
Upvote 0
I would like it to highlight the whole row for that month. The dates are just up top under the days of the week. Everything below that is my moms work area where she wants to plug her figures in


KRice said:
Where do you want the highlighting to appear for holidays, and what would the highlighting look like? Are the cells a certain color...and is it only the day number that is highlighted, or do you want a range of cells extending down the column highlighted on those days?
Click to expand...
 
Upvote 0
Color doesn't really matter just something that is noticeable I was using yellow


KRice said:
Where do you want the highlighting to appear for holidays, and what would the highlighting look like? Are the cells a certain color...and is it only the day number that is highlighted, or do you want a range of cells extending down the column highlighted on those days?
Click to expand...
 
Upvote 0
Here I used the Conditional Formatting rule (apply formatting where a formula evaluates to TRUE) with the following formula:
Excel Formula: 
=ISNUMBER(MATCH(DATE(YEAR($A$10),MONTH($A$10),DAY(B$10)),Holidays!$C$3:$C$10,0))
...and indicated that the formatting rule should be applied to =$B$10:$AL$21
No helper row is needed, just the holidays sheet posted previously (renamed to Holidays). I'm not sure what the A7 formula is doing here.
MrExcel_20220503.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKAL
1Accruals
2
3YearMonthStart Day
42021221: Sunday, 2: Monday
5VacationPersonal Holiday
6NAME:EE:Accrual Level:7Accrual Level:C
72021-2022
8[42]
9MTWTFSSMTWTFSSMTWTFSSMTWTFSSMTWTFSSMT
10Feb-2112345678910111213141516171819202122232425262728         
11Personal Holiday1
12Vacation
13Sick Leave
14Family Sick Leave2
15Admin Leave4
16Bereavement
17Vacation in Lieu of Sick
18FMLA
19PDL
20OPT
21OVT
YearlyCalendar
Cell Formulas
RangeFormula
A7A7=IF($C$4=1,A4,A4&"-"&A4+1)
B9B9=CHOOSE(1+MOD($I$4+1-2,7),"S","M","T","W","T","F","S")
C9C9=CHOOSE(1+MOD($I$4+2-2,7),"S","M","T","W","T","F","S")
D9D9=CHOOSE(1+MOD($I$4+3-2,7),"S","M","T","W","T","F","S")
E9E9=CHOOSE(1+MOD($I$4+4-2,7),"S","M","T","W","T","F","S")
F9F9=CHOOSE(1+MOD($I$4+5-2,7),"S","M","T","W","T","F","S")
G9G9=CHOOSE(1+MOD($I$4+6-2,7),"S","M","T","W","T","F","S")
H9H9=CHOOSE(1+MOD($I$4+7-2,7),"S","M","T","W","T","F","S")
I9:AL9I9=B9
A10A10=DATE($A$4,$C$4,1)
B10B10=IF(WEEKDAY(A10,1)=$I$4,A10,"")
C10C10=IF(B10="",IF(WEEKDAY(A10,1)=MOD($I$4,7)+1,A10,""),B10+1)
D10D10=IF(C10="",IF(WEEKDAY(A10,1)=MOD($I$4+1,7)+1,A10,""),C10+1)
E10E10=IF(D10="",IF(WEEKDAY(A10,1)=MOD($I$4+2,7)+1,A10,""),D10+1)
F10F10=IF(E10="",IF(WEEKDAY(A10,1)=MOD($I$4+3,7)+1,A10,""),E10+1)
G10G10=IF(F10="",IF(WEEKDAY(A10,1)=MOD($I$4+4,7)+1,A10,""),F10+1)
H10H10=IF(G10="",IF(WEEKDAY(A10,1)=MOD($I$4+5,7)+1,A10,""),G10+1)
I10:AL10I10=IF(H10="","",IF(MONTH(H10+1)<>MONTH(H10),"",H10+1))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B10:AL21Expression=ISNUMBER(MATCH(DATE(YEAR($A$10),MONTH($A$10),DAY(B$10)),Holidays!$C$3:$C$10,0))textNO
 
Upvote 0
I noticed that the vertical gray bars denoting weekends do not shift correctly when, for example, you change the start-of-week option in I4 from Monday to Sunday. Here is an idea using conditional formatting that determines whether a number is in row 10 and an "S" is in row 9. If so, then the cells are shaded and the font colors adjusted as necessary. Before implementing this, you will want to select B10:B21 and click on the "paint brush" Format Painter and then swipe across your entire range or C:AL. Then apply the conditional formatting rules to handle the row heading formats, and vertical bar formats for weekends and holidays.
MrExcel_20220503.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKAL
1Accruals
2
3YearMonthStart Day
42021211: Sunday, 2: Monday
5VacationPersonal Holiday
6NAME:EE:Accrual Level:7Accrual Level:C
72021-2022
8[42]
9SMTWTFSSMTWTFSSMTWTFSSMTWTFSSMTWTFSSM
10Feb-2021 12345678910111213141516171819202122232425262728        
11Personal Holiday1
12Vacation
13Sick Leave
14Family Sick Leave2
15Admin Leave4
16Bereavement
17Vacation in Lieu of Sick
18FMLA
19PDL
20OPT
21OVT
YearlyCalendar
Cell Formulas
RangeFormula
A7A7=IF($C$4=1,A4,A4&"-"&A4+1)
B9B9=CHOOSE(1+MOD($I$4+1-2,7),"S","M","T","W","T","F","S")
C9C9=CHOOSE(1+MOD($I$4+2-2,7),"S","M","T","W","T","F","S")
D9D9=CHOOSE(1+MOD($I$4+3-2,7),"S","M","T","W","T","F","S")
E9E9=CHOOSE(1+MOD($I$4+4-2,7),"S","M","T","W","T","F","S")
F9F9=CHOOSE(1+MOD($I$4+5-2,7),"S","M","T","W","T","F","S")
G9G9=CHOOSE(1+MOD($I$4+6-2,7),"S","M","T","W","T","F","S")
H9H9=CHOOSE(1+MOD($I$4+7-2,7),"S","M","T","W","T","F","S")
I9:AL9I9=B9
A10A10=DATE($A$4,$C$4,1)
B10B10=IF(WEEKDAY(A10,1)=$I$4,A10,"")
C10C10=IF(B10="",IF(WEEKDAY(A10,1)=MOD($I$4,7)+1,A10,""),B10+1)
D10D10=IF(C10="",IF(WEEKDAY(A10,1)=MOD($I$4+1,7)+1,A10,""),C10+1)
E10E10=IF(D10="",IF(WEEKDAY(A10,1)=MOD($I$4+2,7)+1,A10,""),D10+1)
F10F10=IF(E10="",IF(WEEKDAY(A10,1)=MOD($I$4+3,7)+1,A10,""),E10+1)
G10G10=IF(F10="",IF(WEEKDAY(A10,1)=MOD($I$4+4,7)+1,A10,""),F10+1)
H10H10=IF(G10="",IF(WEEKDAY(A10,1)=MOD($I$4+5,7)+1,A10,""),G10+1)
I10:AL10I10=IF(H10="","",IF(MONTH(H10+1)<>MONTH(H10),"",H10+1))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B10:AL10Expression=AND(ISNUMBER(B$10),B$9="S")textNO
B11:AL21Expression=AND(ISNUMBER(B$10),B$9="S")textNO
B10:AL21Expression=ISNUMBER(MATCH(DATE(YEAR($A$10),MONTH($A$10),DAY(B$10)),Holidays!$C$3:$C$10,0))textNO
B10:AL10Expression=ISNUMBER(B$10)textNO

For reference, the dark gray is color hex code: #595959 and white font
light gray is: #D9D9D9 and black font
yellow is standard: #FFFF00 and black font
light blue is: #D3EBF3 and black font
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
WORKDAY.INTL does not block out holidays
2
Replies
13
Views
1K
shawnbull
S
C
  • Question Question
Dynamic calendar for parenting time
2
Replies
14
Views
1K
felixstraube
F
C
  • Solved
Formula Help (Calendar) Expanded
2
Replies
15
Views
2K
Eric W
Eric W
J
  • Question Question
Formula for weekdays of date in another cell
Replies
4
Views
1K
Jaxi
J
J
  • Question Question
How to allocate project hours between specific dates using specific daily hour burn rates and including awkward calendar month start/end+holidays
2
Replies
11
Views
2K
felixstraube
F

Forum statistics

Threads
1,223,896
Messages
6,175,264
Members
452,627
Latest member
KitkatToby

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top