Solved

# Holiday/Vacation Planner

Posted on 2011-05-10
400 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

LVL 7

Expert Comment

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.

Let me know if this answers your question
0

LVL 7

Expert Comment

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

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

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

pdvsa,

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

Kevin
0

Author Comment

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

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

I am not sure if that fiel was saved correctly.

Please use this one

Vacations-and-Holiday-Manager.xls
0

LVL 81

Expert Comment

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

¿¿¿¿¿¿¿ ¿¿¿¿¿¿¿. ¿¿¿¿, ¿¿¿ ¿ ¿¿¿¿ ¿¿¿¿¿¿
0

Author Comment

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

## Write Comment

Please enter a first name

Please enter a last name

We will never share this with anyone.

## Featured Post

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

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

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

#### Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!