Solved

# Holiday/Vacation Planner

Posted on 2011-05-10
Medium Priority
441 Views
Hello, I dl this template from Zorvek.  I have a question about the count of days when the vacation day is only 1 day.  to me it does not seem to count the day if the Begin Date and End Date are the same or maybe I dont understand it completely.

Please let me know what you think.  If you believe it should count the day if the begin date = end date then please help modify.

thank you Vacations-and-Holiday-Manager.xls
0
Question by:pdvsa
• 6
• 3
• 2

LVL 7

Expert Comment

ID: 35732329
I'm not sure what the "12 Mo" column should be, but here are the formulas that work for a single day. On the "Schedule" sheet:
Cell F6:

``````=IF(AND(C6>0,D6>0,D6>C6),NETWORKDAYS(MAX(C6,DATE(YEAR(TODAY())-1,1,1)),MIN(D6,DATE(YEAR(TODAY()),1,0)),Holidays!C\$3:C\$42)*OR(YEAR(C6)=YEAR(TODAY())-1,YEAR(D6)=YEAR(TODAY())-1),IF(AND(C6=D6,C6>=DATE(YEAR(TODAY())-1,1,0),C6<DATE(YEAR(TODAY()),1,0)),1,""))
``````

Cell G6:

``````=IF(AND(C7>0,D7>0,D7>C7),NETWORKDAYS(MAX(C7,DATE(YEAR(TODAY()),1,1)),MIN(D7,DATE(YEAR(TODAY())+1,1,0)),Holidays!C\$3:C\$42)*OR(YEAR(C7)=YEAR(TODAY()),YEAR(D7)=YEAR(TODAY())),IF(AND(C7=D7,C7>DATE(YEAR(TODAY()),1,0)),1,""))
``````

Once you enter those, drag them up/down to apply them to all of the lines.  Again, I did not change the "12mo" column as I'm not sure what it should be. Maybe a rolling 12 mo period? (ie within the last 12 mo?) Also note that those years are based on a calendar year - jan 1 to Dec 31.

0

LVL 7

Expert Comment

ID: 35732341
I appologize! Cell G6 should be the following. Please ignore the version above:
``````=IF(AND(C6>0,D6>0,D6>C6),NETWORKDAYS(MAX(C6,DATE(YEAR(TODAY()),1,1)),MIN(D6,DATE(YEAR(TODAY())+1,1,0)),Holidays!C\$3:C\$42)*OR(YEAR(C6)=YEAR(TODAY()),YEAR(D6)=YEAR(TODAY())),IF(AND(C6=D6,C6>=DATE(YEAR(TODAY()),1,0)),1,""))
``````
0

Author Comment

ID: 35733129
Hi, thank you.  I tested and think it is what I need.  Will leave open a bit longer... maybe Zorvek will give me a response as to why it will not count a single day.
0

LVL 81

Accepted Solution

zorvek (Kevin Jones) earned 2000 total points
ID: 35754705
Everything in the workbook is fine except for one part in each of the three formulas. This:

AND(C6>0,D6>0,D6>C6)

should be:

AND(C6>0,D6>0,D6>=C6)

The 12 Mo column is a count of holiday days taken the past 12 months.

Kevin
0

LVL 81

Expert Comment

ID: 35754918
pdvsa,

Where did you find the planner? I want to update that copy.

Kevin
0

Author Comment

ID: 35756849
Ok thank you.

M4: i would like to award you some pts but i think Zorvek had a little "inside information" since he is the author.  Please let me know if you think do not agree.

Thanks to both

Zorvek:  i think i did google search or might have been here on EE.   I was going to post the link but i could not find it either.  My search term was vacation or holiday planner.
0

Author Comment

ID: 35827778
Zorvek,

I am revisiting this.
do you see any issue with the 12 mo column?

thank you Vacations-and-Holiday-Manager.xls
0

Author Comment

ID: 35827788
I am not sure if that fiel was saved correctly.

Vacations-and-Holiday-Manager.xls
0

LVL 81

Expert Comment

ID: 35873870
Change this formula:

=IF(AND(C4>0,D4>0,D4>=C4),NETWORKDAYS(MAX(C4,TODAY()-365),MIN(D4,TODAY()),Holidays!C\$3:C\$42)*(MAX(C4,TODAY()-365)<MIN(D4,TODAY())),"")

to:

=IF(AND(C4>0,D4>0,D4>=C4),NETWORKDAYS(MAX(C4,TODAY()-365),MIN(D4,TODAY()),Holidays!C\$3:C\$42)*(MAX(C4,TODAY()-365)<=MIN(D4,TODAY())),"")

and copy down.

Kevin
0

Author Comment

ID: 35875082
ĀæĀæĀæĀæĀæĀæĀæ ĀæĀæĀæĀæĀæĀæĀæ. ĀæĀæĀæĀæ, ĀæĀæĀæ Āæ ĀæĀæĀæĀæ ĀæĀæĀæĀæĀæĀæ
0

Author Comment

ID: 35875089
thankĀ youĀ veryĀ much.Ā Ā iĀ wishĀ iĀ hadĀ yourĀ knowledge.   (In another country and EE did not like the text.)
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URLās and adds a header titled āURL Listā. It then searches through all URLās in column āAā, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URLās are then highligā¦
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
###### Suggested Courses
Course of the Month16 days, 4 hours left to enroll