Staff Planning

[Silva_Citizen]

Hi
I have a staff planning tool which I have created in excel

Database. Is fed from a data input sheet, and houses formulas to work out lots of variables.

Sheet 2 is a list

Column A - Employee Name

Column B - Inclusive Holiday Date From

Column C - Inclusive Holiday Date To

The idea being that people book a holiday, its recorded here, and then it is planned in in the future

All seemed well until I realised that the formula I was using planned in the earliest booked date, whether or not it was in the past or future.


What I actually want is for the list to choose the employees next or future holiday to and from dates, and put into the workings on sheet 1


So Sheet 1
Column A Column G Column H
Employee Name Next Hol From Next Hol To
Joe
Fred
Peter


Is needed to be fed by

Obviously next holiday must start on or after the date I am planning for but I cant figure out how to make that happen

Sheet 2
A Employee Name B. Hol From Date C Hol To Date
Joe 1/12/18 10/12/18 -- Note Future
Fred 1/8/18 9/8/18 --note in past
Peter 11/7/19 15/7/19
Joe 5/11/18 5/11/18 -- Note before Joe Previous Holiday
Fred 1/12/18 10/12/18 -- Next holiday same date as Joes

Wondering if someone can help please

Regards

 

[mse330]

Hey ... What formulas are you using in Sheet2 to extract the dates From/To ? Please post your existing formulas so we can suggest alternatives

 

[Silva_Citizen]

Hi

And thanks for your reply.

I was using an array formula
=IF($C35="","",MIN(IF('HOLIDAY INPUT'!$A$2:$A$200=FTE!$C31,'HOLIDAY INPUT'!$B$2:$B$200))) to establish the holiday start date and


'=IF($A31="","",MIN(IF('HOLIDAY INPUT'!$A$2:$A$200=FTE!$C31,'HOLIDAY INPUT'!$C$2:$C$200))) to establish the holiday end

* C31 relates to a person in the FTE register (people are in column c) and they are column a on the holiday input

Thank you.

 

[mse330]

Try the below array formulas

Holiday Start Date
=IF($C32="","",MIN(IF($C32='HOLIDAY INPUT'!$A$2:$A$200,IF('HOLIDAY INPUT'!$B$2:$B$200>=TODAY(),'HOLIDAY INPUT'!$B$2:$B$200,""),"")))

Holiday End Date​

=IF($C32="","",MIN(IF($C32='HOLIDAY INPUT'!$A$2:$A$200,IF('HOLIDAY INPUT'!$C$2:$C$200>=TODAY(),'HOLIDAY INPUT'!$C$2:$C$200,""),"")))