Forum Discussion

Carl_61's avatar
Carl_61
Iron Contributor
Nov 25, 2024

Formula Help

I manage buildings with 168 rooms. the occupants of these rooms from time to time are selected to be part of a weekly cleanup crew that are randomly selected.  The formula below provides filters for this random selection.

This formula works but I am having issues with analyzing if the outcome is correct.  I could use some help from those that can see this more clearly than I.  Here is the formula :

=LET( n, FILTER(Room_Roster[NAME], (Room_Roster[NAME]>" ")*(Room_Roster[GNDR]=$E$3)*(Room_Roster[POSITION]<>Lists!L9)*(Room_Roster[Occ Status]="O")*(Room_Roster[BO Selected Date:]="")*(Room_Roster[Extra Duty Status Code]<>2)*(Room_Roster[Extra Duty Status Code]<>3)*(Room_Roster[Date BO Duty Completed]<=EDATE(TODAY(),+Lists!L7))*(Room_Roster[Room Assign/Check-in Date]<=EDATE(TODAY(), +Lists!L5))), count, ROWS(n), SORTBY(n, RANDARRAY(count)))

Most of this is no problem but where I loose the logical path is where the EDATE Functions come into play.  Do I have formula correct to achieve the desired outcome?. Here is the part in question:

(Room_Roster[Room Assign/Check-in Date]<=EDATE(TODAY(), +Lists!L5)

I am not sure if <=EDATE is correct or should it be >=EDATE and should it be +Lists!L5 or -Lists!L5.  Lists!L5 is 6 months in this case. Do I have this formula correct?

Do not select the individual unless TODAY() is =>Lists!L5 months from check-in date: Do not select if individual has not been checked in for at least 6 months or more.

Another words, Do not select if the individual has not been checked in for more than, the number of months defined in Lists!L5.  Lets say the number of months is 6.  The number of months can be adjusted to less or more depending on the availability of people. This is why it is on the Tab Lists in cell L5.

 

I am also trying to determine if another part of this formula is correct.

(Room_Roster[Date BO Duty Completed]<=EDATE(TODAY(),+Lists!L7)

Lists!L7 is 12 for 12 months. 

Trying to ensure once an individual has completed the duty they are not selected again for at least Lists!L7 (12 Months) from the date they completed the duty.

Do not select again unless TODAY() - completion date is => Lists!L7. (12 Months)

Do I have this part of the formula correct?

 

The last issue I have is we don't want to select someone again if they are already selected but they have not completed the BO Duty.

Do not select if "Selected Date" <> "" and Completed Date ="".

 

Over all the objective is to randomly select someone for each Monday to be on the cleanup crew.  Upon being selected, a selection date will be recorded and the individual will be provided with the Monday date they are to start on the cleanup crew.  Once the assignment has been completed a completion date is recorded. Once a selection date has been recorded, the duty date will be in the future.  When a random selection is being made, we don't want the same individual being selected again until after they have completed the Duty and the desired amount of time has passed, per the number in the Lists Tab has passed.  I'd like to have, if possible, the formula clear the selection date and Completed date cells once the number of months defined in LISTS!L7 has passed so the individual goes back into the pool of potential selectees.

 

Can anyone help me with whatever part of this can.

 

Thank you,

 

Carl

 

1 Reply

  • As in my reply to your PM:

    Since you want to select only persons who checked in 6 months or more before the current date, you should use

    (Room_Roster[Room Assign/Check-in Date]<=EDATE(TODAY(), -Lists!L5)

    Similarly, you want to select only persons who completed duty 12 months or more before the current date, so

    (Room_Roster[Date BO Duty Completed]<=EDATE(TODAY(), -Lists!L7)

    You already select only persons where Selected Date is empty, so you don't have to add an extra condition for Selected Date is not empty and Completed Date is empty.

Resources