Excel Calendar program to add vacation days

chris pike
chris pike used Ask the Experts™
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

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.

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)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial