Hello, 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:

=IF(J$6=$D7;IF(J$6<$E7;1;"");IF(J$6>$D7;IF(J$6<$E7;1;"");""))

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.

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

calendarconfigured?MIKE

I made a screenshot. http://img41.imageshack.us/img41/85...

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

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 Rose 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-SepThen 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.

MIKE

It's not pretty, but it seems to work: =IF(ISBLANK($G7),IF(J$6=$D7,IF(J$6<$E7,1,""),IF(J$6>$D7,IF(J$6<$E7,1,""),"")),IF(J$6=$D7,IF(J$6<$E7,1,""),IF(J$6>$D7,IF(J$6<$E7,1,""),""))&IF(J$6=$G7,IF(J$6<$H7,1,""),IF(J$6>$G7,IF(J$6<$H7,1,""),"")))

MIKE

Posted the wrong formula, this one is shorter: =IF(J$6=$D7,IF(J$6<$E7,1,""),IF(J$6>$D7,IF(J$6<$E7,1,""),""))&IF(J$6=$G7,IF(J$6<$H7,1,""),IF(J$6>$G7,IF(J$6<$H7,1,""),""))

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

MIKE

Thank you very much, you're really helpful.

Ask Your Question

Weekly Poll

Do you trust smart speakers to not spy on you?

Discuss in The Lounge

Poll History