We help IT Professionals succeed at work.

Excel Calendar program to add vacation days

chris pike
chris pike asked
on
HI there,
I have an awesome vacation program, that has a dashboard to enter days and into the calendar.
It has worked great for 2016, and now we are getting ready for 2017. The only problem is that there 2 months that have 6 weeks (April and December) These two months do not show that last couple days in the 6th week line.
The VB of course needs to work when info is sent through the dashboard to the newly added days in the 6th week.
   
Thanks so much.
Chris
aprildec.JPGStripped-for-help.xlsm
Comment
Watch Question

chris pikeOffice Admin

Author

Commented:
OPps

7989kerry

second password
vacation
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
Change your formulas as per below
On April Sheet,
In F58
=DaysAndWeeks+DATE(CalendarYear,1,1)-WEEKDAY(DATE(CalendarYear,1,1),(WeekStart="Mon")+1)+120

Open in new window

Confirm with Ctrl+Shift+Enter instead of Enter alone.

On December Sheet,
In F58
=DaysAndWeeks+DATE(CalendarYear,1,1)-WEEKDAY(DATE(CalendarYear,1,1),(WeekStart="Mon")+1)+365

Open in new window

Confirm with Ctrl+Shift+Enter instead of Enter alone.
chris pikeOffice Admin

Author

Commented:
You are a wizard.
Awesome work...
Thanks so much,
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome Chris! Glad to help.
Wait... I am Genius now with level 26. Lol (just kidding)