Dynamic calendar for parenting time

C

CigarBuddha

New Member
Joined
Jan 17, 2024
Messages
4
Office Version
  1. 365
Platform
  1. Windows
I have a dynamic calendar setup and want to have it show the days each parent has and show the holidays. With that in mind the holidays change as one will have an odd year one will have an even year. The way the schedule is set one parent has Monday and Tuesday one parent has Wednesday and Thursday then every other weekend starting on Friday. If at all possible some of the dates need to have times associated with them like from 10am on that holiday to 10am the next day.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
The following may help.

1. One formula creates the Calendar 365 or 366 days
2. The entire range is converted to Values.
3. The dates, cell width and height, etc. can be formatted to your preferences.
4. If you require monthly information, add a column for month and filter by month.
5. Fill known information with a distinct color.
6, link special information
7. add time to the dates

Your could add a column and filter by month
You can link specific holidays etc.

Calendar 2024.xlsx
CDEFG
1TueWedThursFriSat
202-Jan-2403-Jan-2404-Jan-2405-Jan-2406-Jan-24
309-Jan-2410-Jan-2411-Jan-2412-Jan-2413-Jan-24
416-Jan-2417-Jan-2418-Jan-2419-Jan-2420-Jan-24
523-Jan-2424-Jan-2425-Jan-2426-Jan-2427-Jan-24
630-Jan-2431-Jan-2401-Feb-2402-Feb-2403-Feb-24
Year
 
Upvote 0
How about something like this?
In C2 and C3 you enter year and month, then you get the month displayed bellow so that the days for parent 1 are painted in one color, and the days for parent 2 are painted in another color.
It is set so that Parent 1 gets Wed and Thu, and even weekend (starting to count from 05/01/2024), and Parent 2 gets Mon and Tue, and every odd weekend.
Then you have 2 tables, where you can load the holidays for each parent (a list of all the days in that holiday is required). Holidays take precedence over the normal calendar. Just to exemplify I added some dates for Jan 2024. But you can populated them as you like.

To create this i created two named ranges:
With cell B7 selected (this is important as they have relative references) create range "Parent_1" with this formula:
Excel Formula: 
=LET( d, Sheet1!B7, wd, WEEKDAY(d,2), holP1, IFERROR(XMATCH(d,HP_1[P1],0),0), holP2, IFERROR(XMATCH(d,HP_2[P2],0),0), IF(d<>"", ((wd>2)*(wd<5) + (wd>4)*ISEVEN(INT((d-45296)/7)) + holP1)*NOT(holP2), 0) )

Also with cell B7 selected create range "Parent_2" with this formula:
Excel Formula: 
=LET( d, Sheet1!B7, wd, WEEKDAY(d,2), holP1, IFERROR(XMATCH(d,HP_1[P1],0),0), holP2, IFERROR(XMATCH(d,HP_2[P2],0),0), IF(d<>"", ((wd<3) + (wd>4)*ISODD(INT((d-45296)/7)) + holP2) * NOT(holP1), 0) )

Then create two conditional formatting rules:

1705577344302.png


Here is also a link to download the working file:

ParentingCalendar.zip

ParentingCalendar - copia.xlsx
ABCDEFGHIJKLM
1
2Year2024Parent 1
3Month2Parent 2
4
5February 2024HolidaysHolidays
6SunMonTueWedThuFriSatP1P2
7 01/02/202402/02/202403/02/202401/01/202421/01/2024
804/02/202405/02/202406/02/202407/02/202408/02/202409/02/202410/02/202402/01/202422/01/2024
911/02/202412/02/202413/02/202414/02/202415/02/202416/02/202417/02/202403/01/202423/01/2024
1018/02/202419/02/202420/02/202421/02/202422/02/202423/02/202424/02/202404/01/202424/01/2024
1125/02/202426/02/202427/02/202428/02/202429/02/202405/01/202425/01/2024
1206/01/202426/01/2024
1307/01/202427/01/2024
1408/01/202428/01/2024
1509/01/202429/01/2024
1610/01/202430/01/2024
1711/01/202431/01/2024
1812/01/2024
1913/01/2024
2014/01/2024
21
Sheet1
Cell Formulas
RangeFormula
E5E5=TEXT(E9,"mmmm yyyy")
B6:H6B6=TEXT(SEQUENCE(,7),"ddd")
B7:H11B7=LET(y, $C$2, m, $C$3, firstDay, DATE(y,m,1), md, SEQUENCE(,EOMONTH(firstDay,0)-firstDay+1,firstDay), start, IF(SEQUENCE(,WEEKDAY(firstDay,1),,0),""), ret, IFERROR(WRAPROWS(DROP(HSTACK(start,md),,1),7),""), ret )
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B7:H12Expression=Parent_2textNO
B7:H12Expression=Parent_1textNO
Cells with Data Validation
CellAllowCriteria
C3List1,2,3,4,5,6,7,8,9,10,11,12


So show how they work for the holidays:

ParentingCalendar - copia.xlsx
ABCDEFGHIJKLM
1
2Year2024Parent 1
3Month1Parent 2
4
5January 2024HolidaysHolidays
6SunMonTueWedThuFriSatP1P2
7 01/01/202402/01/202403/01/202404/01/202405/01/202406/01/202401/01/202421/01/2024
807/01/202408/01/202409/01/202410/01/202411/01/202412/01/202413/01/202402/01/202422/01/2024
914/01/202415/01/202416/01/202417/01/202418/01/202419/01/202420/01/202403/01/202423/01/2024
1021/01/202422/01/202423/01/202424/01/202425/01/202426/01/202427/01/202404/01/202424/01/2024
1128/01/202429/01/202430/01/202431/01/202405/01/202425/01/2024
1206/01/202426/01/2024
1307/01/202427/01/2024
1408/01/202428/01/2024
1509/01/202429/01/2024
1610/01/202430/01/2024
1711/01/202431/01/2024
1812/01/2024
1913/01/2024
2014/01/2024
21
Sheet1
Cell Formulas
RangeFormula
E5E5=TEXT(E9,"mmmm yyyy")
B6:H6B6=TEXT(SEQUENCE(,7),"ddd")
B7:H11B7=LET(y, $C$2, m, $C$3, firstDay, DATE(y,m,1), md, SEQUENCE(,EOMONTH(firstDay,0)-firstDay+1,firstDay), start, IF(SEQUENCE(,WEEKDAY(firstDay,1),,0),""), ret, IFERROR(WRAPROWS(DROP(HSTACK(start,md),,1),7),""), ret )
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B7:H12Expression=Parent_2textNO
B7:H12Expression=Parent_1textNO
Cells with Data Validation
CellAllowCriteria
C3List1,2,3,4,5,6,7,8,9,10,11,12
 
Upvote 0
felixstraube said:
How about something like this?
In C2 and C3 you enter year and month, then you get the month displayed bellow so that the days for parent 1 are painted in one color, and the days for parent 2 are painted in another color.
It is set so that Parent 1 gets Wed and Thu, and even weekend (starting to count from 05/01/2024), and Parent 2 gets Mon and Tue, and every odd weekend.
Then you have 2 tables, where you can load the holidays for each parent (a list of all the days in that holiday is required). Holidays take precedence over the normal calendar. Just to exemplify I added some dates for Jan 2024. But you can populated them as you like.

To create this i created two named ranges:
With cell B7 selected (this is important as they have relative references) create range "Parent_1" with this formula:
Excel Formula: 
=LET( d, Sheet1!B7, wd, WEEKDAY(d,2), holP1, IFERROR(XMATCH(d,HP_1[P1],0),0), holP2, IFERROR(XMATCH(d,HP_2[P2],0),0), IF(d<>"", ((wd>2)*(wd<5) + (wd>4)*ISEVEN(INT((d-45296)/7)) + holP1)*NOT(holP2), 0) )

Also with cell B7 selected create range "Parent_2" with this formula:
Excel Formula: 
=LET( d, Sheet1!B7, wd, WEEKDAY(d,2), holP1, IFERROR(XMATCH(d,HP_1[P1],0),0), holP2, IFERROR(XMATCH(d,HP_2[P2],0),0), IF(d<>"", ((wd<3) + (wd>4)*ISODD(INT((d-45296)/7)) + holP2) * NOT(holP1), 0) )

Then create two conditional formatting rules:

View attachment 105296

Here is also a link to download the working file:

ParentingCalendar.zip

ParentingCalendar - copia.xlsx
ABCDEFGHIJKLM
1
2Year2024Parent 1
3Month2Parent 2
4
5February 2024HolidaysHolidays
6SunMonTueWedThuFriSatP1P2
7 01/02/202402/02/202403/02/202401/01/202421/01/2024
804/02/202405/02/202406/02/202407/02/202408/02/202409/02/202410/02/202402/01/202422/01/2024
911/02/202412/02/202413/02/202414/02/202415/02/202416/02/202417/02/202403/01/202423/01/2024
1018/02/202419/02/202420/02/202421/02/202422/02/202423/02/202424/02/202404/01/202424/01/2024
1125/02/202426/02/202427/02/202428/02/202429/02/202405/01/202425/01/2024
1206/01/202426/01/2024
1307/01/202427/01/2024
1408/01/202428/01/2024
1509/01/202429/01/2024
1610/01/202430/01/2024
1711/01/202431/01/2024
1812/01/2024
1913/01/2024
2014/01/2024
21
Sheet1
Cell Formulas
RangeFormula
E5E5=TEXT(E9,"mmmm yyyy")
B6:H6B6=TEXT(SEQUENCE(,7),"ddd")
B7:H11B7=LET(y, $C$2, m, $C$3, firstDay, DATE(y,m,1), md, SEQUENCE(,EOMONTH(firstDay,0)-firstDay+1,firstDay), start, IF(SEQUENCE(,WEEKDAY(firstDay,1),,0),""), ret, IFERROR(WRAPROWS(DROP(HSTACK(start,md),,1),7),""), ret )
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B7:H12Expression=Parent_2textNO
B7:H12Expression=Parent_1textNO
Cells with Data Validation
CellAllowCriteria
C3List1,2,3,4,5,6,7,8,9,10,11,12


So show how they work for the holidays:

ParentingCalendar - copia.xlsx
ABCDEFGHIJKLM
1
2Year2024Parent 1
3Month1Parent 2
4
5January 2024HolidaysHolidays
6SunMonTueWedThuFriSatP1P2
7 01/01/202402/01/202403/01/202404/01/202405/01/202406/01/202401/01/202421/01/2024
807/01/202408/01/202409/01/202410/01/202411/01/202412/01/202413/01/202402/01/202422/01/2024
914/01/202415/01/202416/01/202417/01/202418/01/202419/01/202420/01/202403/01/202423/01/2024
1021/01/202422/01/202423/01/202424/01/202425/01/202426/01/202427/01/202404/01/202424/01/2024
1128/01/202429/01/202430/01/202431/01/202405/01/202425/01/2024
1206/01/202426/01/2024
1307/01/202427/01/2024
1408/01/202428/01/2024
1509/01/202429/01/2024
1610/01/202430/01/2024
1711/01/202431/01/2024
1812/01/2024
1913/01/2024
2014/01/2024
21
Sheet1
Cell Formulas
RangeFormula
E5E5=TEXT(E9,"mmmm yyyy")
B6:H6B6=TEXT(SEQUENCE(,7),"ddd")
B7:H11B7=LET(y, $C$2, m, $C$3, firstDay, DATE(y,m,1), md, SEQUENCE(,EOMONTH(firstDay,0)-firstDay+1,firstDay), start, IF(SEQUENCE(,WEEKDAY(firstDay,1),,0),""), ret, IFERROR(WRAPROWS(DROP(HSTACK(start,md),,1),7),""), ret )
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B7:H12Expression=Parent_2textNO
B7:H12Expression=Parent_1textNO
Cells with Data Validation
CellAllowCriteria
C3List1,2,3,4,5,6,7,8,9,10,11,12
Click to expand...
Thank you so much there is a lot that will help here. I do have one question with the Holidays. So like for me I have Easter this year and my ex has Easter next year they are all based on odd and even years will that formula you came up with work that way?
 
Upvote 0
Glad to help.
Yes, it will work. You just have to enter in one table (parent 1) the easter holidays of this year, and in the other table the easter holidays of next year.
Try entering them and selecting the year and month and see if it works ok. It should work. Let me know.
 
Upvote 0
I this case, if you are parent 1you will have those days anyway (i dont know if i got the right dates for easter 2024).

ParentingCalendar.xlsx
ABCDEFGHIJKL
1
2Year2024CigarBuddha
3Month3Ex
4
5March 2024HolidaysHolidays
6SunMonTueWedThuFriSatCigarBuddhaEx
7 1228/03/202417/04/2025
8345678929/03/202418/04/2025
91011121314151630/03/202419/04/2025
101718192021222331/03/202420/04/2025
1124252627282930
1231
Sheet1
Cell Formulas
RangeFormula
E5E5=TEXT(E9,"mmmm yyyy")
B6:H6B6=TEXT(SEQUENCE(,7),"ddd")
B7:H12B7=LET(y, $C$2, m, $C$3, firstDay, DATE(y,m,1), md, SEQUENCE(,EOMONTH(firstDay,0)-firstDay+1,firstDay), start, IF(SEQUENCE(,WEEKDAY(firstDay,1),,0),""), ret, IFERROR(WRAPROWS(DROP(HSTACK(start,md),,1),7),""), ret )
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B7:H12Expression=Parent_2textNO
B7:H12Expression=Parent_1textNO
Cells with Data Validation
CellAllowCriteria
C3List1,2,3,4,5,6,7,8,9,10,11,12


And your ex (if parent 2) will get the extra 17/04/2025
ParentingCalendar.xlsx
ABCDEFGHIJKL
1
2Year2025CigarBuddha
3Month4Ex
4
5April 2025HolidaysHolidays
6SunMonTueWedThuFriSatCigarBuddhaEx
7 1234528/03/202417/04/2025
8678910111229/03/202418/04/2025
91314151617181930/03/202419/04/2025
102021222324252631/03/202420/04/2025
1127282930
12
Sheet1
Cell Formulas
RangeFormula
E5E5=TEXT(E9,"mmmm yyyy")
B6:H6B6=TEXT(SEQUENCE(,7),"ddd")
B7:H11B7=LET(y, $C$2, m, $C$3, firstDay, DATE(y,m,1), md, SEQUENCE(,EOMONTH(firstDay,0)-firstDay+1,firstDay), start, IF(SEQUENCE(,WEEKDAY(firstDay,1),,0),""), ret, IFERROR(WRAPROWS(DROP(HSTACK(start,md),,1),7),""), ret )
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B7:H12Expression=Parent_2textNO
B7:H12Expression=Parent_1textNO
Cells with Data Validation
CellAllowCriteria
C3List1,2,3,4,5,6,7,8,9,10,11,12
 
Upvote 0
felixstraube said:
I this case, if you are parent 1you will have those days anyway (i dont know if i got the right dates for easter 2024).

ParentingCalendar.xlsx
ABCDEFGHIJKL
1
2Year2024CigarBuddha
3Month3Ex
4
5March 2024HolidaysHolidays
6SunMonTueWedThuFriSatCigarBuddhaEx
7 1228/03/202417/04/2025
8345678929/03/202418/04/2025
91011121314151630/03/202419/04/2025
101718192021222331/03/202420/04/2025
1124252627282930
1231
Sheet1
Cell Formulas
RangeFormula
E5E5=TEXT(E9,"mmmm yyyy")
B6:H6B6=TEXT(SEQUENCE(,7),"ddd")
B7:H12B7=LET(y, $C$2, m, $C$3, firstDay, DATE(y,m,1), md, SEQUENCE(,EOMONTH(firstDay,0)-firstDay+1,firstDay), start, IF(SEQUENCE(,WEEKDAY(firstDay,1),,0),""), ret, IFERROR(WRAPROWS(DROP(HSTACK(start,md),,1),7),""), ret )
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B7:H12Expression=Parent_2textNO
B7:H12Expression=Parent_1textNO
Cells with Data Validation
CellAllowCriteria
C3List1,2,3,4,5,6,7,8,9,10,11,12


And your ex (if parent 2) will get the extra 17/04/2025
ParentingCalendar.xlsx
ABCDEFGHIJKL
1
2Year2025CigarBuddha
3Month4Ex
4
5April 2025HolidaysHolidays
6SunMonTueWedThuFriSatCigarBuddhaEx
7 1234528/03/202417/04/2025
8678910111229/03/202418/04/2025
91314151617181930/03/202419/04/2025
102021222324252631/03/202420/04/2025
1127282930
12
Sheet1
Cell Formulas
RangeFormula
E5E5=TEXT(E9,"mmmm yyyy")
B6:H6B6=TEXT(SEQUENCE(,7),"ddd")
B7:H11B7=LET(y, $C$2, m, $C$3, firstDay, DATE(y,m,1), md, SEQUENCE(,EOMONTH(firstDay,0)-firstDay+1,firstDay), start, IF(SEQUENCE(,WEEKDAY(firstDay,1),,0),""), ret, IFERROR(WRAPROWS(DROP(HSTACK(start,md),,1),7),""), ret )
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B7:H12Expression=Parent_2textNO
B7:H12Expression=Parent_1textNO
Cells with Data Validation
CellAllowCriteria
C3List1,2,3,4,5,6,7,8,9,10,11,12
Click to expand...
Thank you. Now would I have to have a table that has the dates of the holidays for every year since the dynamic calendar I will be able to change the year and the days change automatically?
 
Upvote 0
Yes, you will need to add the dates of the upcoming years to the tables, especially for holidays like Easter, which falls on a different date every year.
Just add dates to the tables
 
Upvote 0
I did not include the formula for making the calendar in my post.
The formula is shown below.
You could adjust the formula to make the calendar for 2 years.

Calendar 2024.xlsx
ABCDEFGHIJ
1SunMonTueWedThursFriSatCalendar formula for year →01-Jan-24
21-Jan-242-Jan-243-Jan-244-Jan-245-Jan-246-Jan-24A1=CalendarforYear(I1)
37-Jan-248-Jan-249-Jan-2410-Jan-2411-Jan-2412-Jan-2413-Jan-24Format with your preferences
Sheet2
Cell Formulas
RangeFormula
A1:G55A1=CalendarForYear(J1)
H1H1="Calendar formula for year "&"→"
Dynamic array formulas.
Lambda Functions
NameFormula
CalendarForYear=LAMBDA(Date,VSTACK({"Sun","Mon","Tue","Wed","Thurs","Fri","Sat"},LET(dt,SEQUENCE(54,7,WORKDAY.INTL(Date+1,-1,"1111110"),1),IF(YEAR(dt)=YEAR(Date),dt,""))))
 
Upvote 0
Calendar 2024.xlsx
ABCDEFGHIJ
1SunMonTueWedThursFriSatCalendar formula for year →01-Jan-24
21-Jan-242-Jan-243-Jan-244-Jan-245-Jan-246-Jan-24A1=CalendarforYear(I1)
37-Jan-248-Jan-249-Jan-2410-Jan-2411-Jan-2412-Jan-2413-Jan-24Format with your preferences
Sheet2
Cell Formulas
RangeFormula
A1:G55A1=CalendarForYear(J1)
H1H1="Calendar formula for year "&"→"
Dynamic array formulas.
Lambda Functions
NameFormula
CalendarForYear=LAMBDA(Date,VSTACK({"Sun","Mon","Tue","Wed","Thurs","Fri","Sat"},LET(dt,SEQUENCE(54,7,WORKDAY.INTL(Date+1,-1,"1111110"),1),IF(YEAR(dt)=YEAR(Date),dt,""))))
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Subtracting all calendar days, but end date cannot fall on a weekend or holiday. If it does, the end date shifts to the preceding work day.
Replies
8
Views
251
Romy
R
E
  • Solved
Unexpected flaw in WORKDAY formula to calculate date
Replies
7
Views
351
KRice
K
C
  • Solved
Formula Help (Calendar) Expanded
2
Replies
15
Views
719
Eric W
Eric W
J
  • Question
Formula for weekdays of date in another cell
Replies
4
Views
479
Jaxi
J
J
  • Question
Shifting multiple cells left/right and ignoring certain columns?
Replies
2
Views
106
joshpep96
J

Forum statistics

Threads
1,215,073
Messages
6,122,975
Members
449,095
Latest member
Mr Hughes

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