Nested IF statement

Microsoft Excel 2003 (full)
May 7, 2010 at 07:02:49
Specs: Windows XP

I have to make a table with the holiday plans for all our employees. On one side you enter the days for the vacations, on the other side there is a calendar made from cells with the weeks in which you can go on vacation.

The table is made so that you input the first day of the vacation, and the last, and then in the calendar cells it shows a "1" for every day you're on holiday.

The formula I'm using is:


Where J6 is the first week in the calendar, D7 the first day of the vacation, and E7 the last day of the vacation.

The problem is with people who want to go on vacation twice, for example one week in june, and another week in august.

How can i solve this?

Making another column with G7 and H7 as the second date entry, and then a nested IF statement in the calendar? If so, can you help me with the formula?

Thank you very much.

See More: Nested IF statement

Report •

May 7, 2010 at 08:39:12
Sorry, but I can't visualize what your doing.

I take it columns A - C are the names of the employees?

Why does adding an additional row with the employees name cause problems?

How do you have your calendar configured?


Report •

May 7, 2010 at 08:52:27
I made a screenshot.

I'm using conditional formatting to color the cells with the data (the "1"), but that's purely cosmetic.

Report •

May 7, 2010 at 12:25:55
The simplest way is to have them add the second week request to the bottom of the list:

Name		1st Wk From	1st Wk To
John		26-Jun	         4-Jul
Jack		8-Jul	        25-Jul
Mark		17-Jul	         1-Aug
Joe		14-Aug	        29-Aug
Ann		30-Jul	        15-Aug
Mary		3-Jul	        18-Jul
Christy		26-Jun	        11-Jul
Daniel		17-Jul	         1-Aug
Paul		10-Jul	        25-Jul
Rudy		19-Jun	        27-Jun
		2nd Wk From	2nd Wk To
John		14-Aug	        22-Aug
Christy		29-Aug	         5-Sep
Daniel		5-Sep	        12-Sep
Paul		12-Sep	        19-Sep
Rudy		28-Aug	         5-Sep

Then just copy down your original formula and your set to go.

Then once everyone have chosen, you could sort columns B through H on the Name to pretty it up.

I'll play around and see it I can't get it to work your way.


Report •

Related Solutions

May 7, 2010 at 14:08:57
It's not pretty, but it seems to work:



Report •

May 7, 2010 at 17:47:12
Posted the wrong formula, this one is shorter:


The other one works, but I don't think doing a check on cell G7 saves you much time in this instance.


Report •

May 8, 2010 at 00:21:08
Thank you very much, you're really helpful.

Report •

May 8, 2010 at 07:25:11
Glad I could help.


Report •

Ask Question