Finding the next date that fits multiple criteria - Complex formula.

[JulesR]

Hi,

So far in my excel journey, I have managed to find solutions for my needs from other answers on here, but this one has me stumped. I am having difficulty seeing where this formula needs to go, so thought I might ask this forum! In the attached file, I have a tab called Leave, which in the original document is being used to track and map all Annual Leave and Lieu days for a staff of around 60. I have only left 1 name and all the corresponding schedules and personal tabs, as if it works for 1 it will work for all...

The premise is this: On the Leave Tab

The first 5 rows are the master information that applies to everyone and is taken in part from the schedule tabs.

ROW 1 = Days of the week repeated weekly - this is just text
ROW 2 = Week Number - not needed in this context
ROW 3 = Dates from 01/06/14-31/05/15 (Our contractual year) - These are unique and the first one references the cell 'Week 23'!$B$2 and all the rest are using a Cell +1 formula.
ROW 4 = Information referenced from each weekly schedule including Public Holidays
ROW 5 = Public Holiday as the only text referenced from the cell above. Result is either Public Holiday or Blank.

The following rows reference the first staff member and will be repeated for every staff member.

ROW 6 = The start time referenced from each person on each weekly schedule. If they didn't work that day, this returns BLANK. (This row will be hidden in final view)
ROW 7 = Leave / Lieu Day information (all other information not visible through conditional formatting) - referenced from each person on each weekly schedule.
ROW 8 = Where I need help!

If you have read this far - thank you!

In ROW 8, I need a date returned when the cell above reads either Lieu-PH, Lieu-Friday or Lieu-Saturday (in that order). If the cell reads Lieu-PH, I need the date to be the earliest date that was a Public Holiday and was worked AND has not already been used (ie every date in the row must be unique). Likewise for Fridays and Saturdays. I am not sure if it is even possible, but I won't know if I don't ask right?

I can piece together some of the arguments, but I cannot get them to go together in a way which returns anything. There are way too many arguments for nested IFs and there must be a way to do this - maybe using INDEX. I have managed to create lists of the dates for each category in the Dropdown Tab, but I haven't been able to incorporate them in a useful way and am not sure if they are even necessary at this point.

So for cell B8, the idea is that if B6>0 and B7 reads any of the 3 Lieu categories, B8=the last date from the relevant list, where the corresponding cell in Row 6 is >0 and that date is not already in Row 8.

Thanks in advance for any help.

Julie.

[Bernie Deitrick]

Try this one - in cell B8, array-enter the formula (enter using Ctrl-Shift Enter) and copy to the right:

=IF(B7="Lieu-PH",MIN(IF($B$5:$NB$5="Public Holiday",IF($B6:$NB6>0,IF(ISERROR(MATCH($B$3:$NB$3,$A9:A9,FALSE)),$B$3:$NB$3)))),IF(B7="Lieu-Friday",MIN(IF($B$1:$NB$1="Friday",IF($B6:$NB6>0,IF(ISERROR(MATCH($B$3:$NB$3,$A9:A9,FALSE)),$B$3:$NB$3)))),IF(B7="Lieu-Saturday",MIN(IF($B$1:$NB$1="Saturday",IF($B6:$NB6>0,IF(ISERROR(MATCH($B$3:$NB$3,$A9:A9,FALSE)),$B$3:$NB$3)))),"")))

I made some other changes to make the sheet a little easier to maintain - instead of creating links to each sheet, the formulas use INDIRECT functions based on the sheet names in row 2. I also included a row for end time, just to show you how to do that in case you needed it, so take a look.

2014-15 Schedule - SAMPLE 2 formulas.xlsx

[JulesR]

Thank you Bernie,

I have one further question, which I realise I didn't mention in the initial query. Because of how the Public Holiday system works here, these days move every year and are unknown until anything from a day before to a week before. Not a problem, but sometimes, it means a Public Holiday will fall on a Friday or a Saturday. Is it possible to therefore exclude any dates that are public holidays from the Friday and Saturday lists? On your example, cell KU9 returns 0/01/1900 as a default, as Friday the 23rd was a Public Holiday, but that date was already used for a Lieu Friday in Cell IU9.

Otherwise, this does exactly what I asked for - Thank you so much!

Cheers,

Julie.

[Bernie Deitrick]

I posted the formula for B9 instead of B8 - it was referring to row 9 instead of 8 when it was checking for earlier dates. But I have fixed that, and to do what you want, use this in B8 (for sure, this time) - it's an array formula, so enter with Ctrl-Shift-Enter:

=IF(B7="Lieu-PH",MIN(IF($B$5:$NB$5="Public Holiday",IF($B6:$NB6>0,IF(ISERROR(MATCH($B$3:$NB$3,$A8:A8,FALSE)),$B$3:$NB$3)))),IF(B7="Lieu-Friday",MIN(IF($B$1:$NB$1="Friday",IF($B$5:$NB$5<>"Public Holiday",IF($B6:$NB6>0,IF(ISERROR(MATCH($B$3:$NB$3,$A8:A8,FALSE)),$B$3:$NB$3))))),IF(B7="Lieu-Saturday",MIN(IF($B$1:$NB$1="Saturday",IF($B$5:$NB$5<>"Public Holiday",IF($B6:$NB6>0,IF(ISERROR(MATCH($B$3:$NB$3,$A8:A8,FALSE)),$B$3:$NB$3))))),"")))

Then copy to the right.

[JulesR]

Perfect - thank you again Bernie! That does the trick