How to validate dates in Excel; IF or ARRAY or both?

[DAD]

Hi everybody,

First od all I would like to say a big thank you to all. I have browsed this forum for quite a while now and picked up many great tips. The time taken to help others is fantastic.

Ok, now on to my problem that I have been unable to solve, despite many hours of trawling trying to find the correct answer. Unfortunately I am unable to use VBA to help with this as I am running Excel for Mac.

I have attached simplified example of a spreedsheet that illustrates what I am trying to achieve. Essentially, I am looking to automate a vacation planner for the office. In the example shown, we have four employees. Column C shows how much vacation days they have outstanding for that year. Col D and E shows their first period of vacation requested, and Col G and H show the second period of vacation requested. However, the calculation, or check that needs to be done is three fold. Firstly, to check that they have enough vacation days owing, second, that they do not exceed the maximum days allowed for one period shown in E10, and finally, that the total amount of people on vacation do not exceed the maximum amount shown in E11. I would like to validate each period of vacation separately so that the employees know which period they must modify to validate the roster. I am treating it as a first come, first served for validation purposes when requesting vacation.

I hope this explanation is sufficient.

Thank you in advance for your help.

[Cheeky Charlie]

In principle, nested ifs:

if(first criterion, if(second criterion, if(third criterion, allow, deny), deny), deny)

Does that make sense?

CC

[DAD]

Hi CC,

Thanks for the reply. It does make sense in that I have tried to nest IF's but the permutation was just too large to compare all the possibilities. I tried to use an array nested in an IF, but could not get that to work correctly. But thanks for the reply anyway.

[daddylonglegs]

The check to see the number on vacation at any one time is quite tricky......

This formula in F4 copied down will give you the 3 checks considering just the period1 date ranges (although could be extended to period 2)

=IF(D4="","",IF(OR(E4-D4+1>C4,E4-D4+1>E$10,SUM(IF(MMULT((TRANSPOSE(D$4:D4)<=ROW(INDIRECT(D4&":"&E4)))*(TRANSPOSE(E$4:E4)>= ROW(INDIRECT(D4&":"&E4))),(ROW(D$4:D4)^0=1)+0)>E$11,1))>0),"No","Yes"))

That's an array formula that needs to be confirmed with CTRL+SHIFT+ENTER

For the overlaps the formula just looks up the column......so if E11 is 1 and Smith and Jones overlap then only the latter, Jones, is shown as invalid

That doesn't take weekends in to account - do you need to exclude Saturdays and Sundays when considering days taken or max number of days in a row?

[DAD]

Hi DaddyLongLegs,

Wow... thanks very much for that. There is no way I would have been able to come up with a solution like that. I have plugged it in, and it works, so a big thank you for that. Two more questions though; Firstly, how do I add the second period into the formula so that it still works. Secondly, currently this formula works on a top down priority, that is, if only one person is allowed vacation at any one time, the person at the top of the list will be given a "Yes" answer even if the person below him entered their dates first. Is there a way to allocate the "Yes" response based on who entered the data first?

And to answer your question, I do not need to exclude weekends, just to keep it simple.

Once again, thanks very much for the response, it is much appreciated.

[daddylonglegs]

Using worksheet formulas it won't be possible to differentiate based on which was entered first.

You could possibly use Data validation for that which would actually prevent a date range from being entered if it broke the constraints, in which case that would effectively apply the "first come first served" option.

Let me know if you might want to pursue that option. You'll need to use formulas similar to those below in data validation but they'll need to be modified because as they are they are too long to be used....

To take into account the second periods try this formula in F4 copied down

=IF(D4="","",IF(OR(E4-D4+1>C4,E4-D4+1>E$10,SUM(IF(MMULT((TRANSPOSE(D$4:D$7)<=ROW(INDIRECT(D4&":"&E4)))*(TRANSPOSE(E$4:E$7)>= ROW(INDIRECT(D4&":"&E4))),(ROW(D$4:D$7)^0=1)+0)>E$11,1))>0,SUM(IF(MMULT((TRANSPOSE(G$4:G$7)<= ROW(INDIRECT(D4&":"&E4)))*(TRANSPOSE(H$4:H$7)>=ROW(INDIRECT(D4&":"&E4))),(ROW(G$4:G$7)^0=1)+0)>E$11-1,1))>0),"No","Yes"))

and in I4

=IF(G4="","",IF(OR(H4-G4+E4-D4+2>C4,H4-G4+1>E$10,SUM(IF(MMULT((TRANSPOSE(D$4:D$7)<=ROW(INDIRECT(G4&":"&H4)))*(TRANSPOSE(E$4:E$7)>= ROW(INDIRECT(G4&":"&H4))),(ROW(D$4:D$7)^0=1)+0)>E$11-1,1))>0,SUM(IF(MMULT((TRANSPOSE(G$4:G$7)<= ROW(INDIRECT(G4&":"&H4)))*(TRANSPOSE(H$4:H$7)>= ROW(INDIRECT(G4&":"&H4))),(ROW(G$4:G$7)^0=1)+0)>E$11,1))>0),"No","Yes"))

confirmed with CTRL+SHIFT+ENTER as before

These versions will check all date ranges so if E11 is 1 and two ranges overlap they'll both show "No".

In column F only period 1 is checked against "days owing" but in column I the total for both periods is checked

[DAD]

Hi again DaddyLongLegs,

Thanks once again for your help, I have been trying to nut this one out for days. I will have a good look at this in the morning and get back to you. Maybe the data validation idea would be best in this case, but I will need to do some reading up on that first as I don't really know much about it.

Thanks again

[DAD]

Hi again DaddyLongLegs,

I have spent most of today trying to figure out how to use the data validation to perform the function I need, but unfortunately to no avail. I also tried to get around the problem of prioritising by date by inserting an extra column for each period and auto inserting the modified date and time, then incorporating this into the IF function you created. The problem here is that as I am using Excel for Mac, I cannot auto insert the date and time when a cell is modified because I cannot use VB code. Very frustrating!

Anyways, if you have time, I would greatly appreciate your help working the data validation formulas to solve this problem.

Thank you again