?
Solved

Holiday/Vacation Planner

Posted on 2011-05-10
11
Medium Priority
?
441 Views
Last Modified: 2012-08-13
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
Comment
Question by:pdvsa
  • 6
  • 3
  • 2
11 Comments
 
LVL 7

Expert Comment

by:m4trix
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,""))

Open in new window


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,""))

Open in new window


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.

Let me know if this answers your question
0
 
LVL 7

Expert Comment

by:m4trix
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,""))

Open in new window

0
 

Author Comment

by:pdvsa
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 81

Accepted Solution

by:
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

by:zorvek (Kevin Jones)
ID: 35754918
pdvsa,

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

Kevin
0
 

Author Comment

by:pdvsa
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

by:pdvsa
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

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

Please use this one

 Vacations-and-Holiday-Manager.xls
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
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

by:pdvsa
ID: 35875082
¿¿¿¿¿¿¿ ¿¿¿¿¿¿¿. ¿¿¿¿, ¿¿¿ ¿ ¿¿¿¿ ¿¿¿¿¿¿
0
 

Author Comment

by:pdvsa
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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

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.

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question