selecting a date from a list of pre-defined dates

[Blanchetdb]

Hi

I require a cell to populate with a date under the following conditions:

First: the employee selects an option, from a dropdown menu, a type of staffing action ... cell (Y41)
Second: cell (K10) auto-populates with Todays date
Third: the system calculates the amount of working days from Todays date + "x" amount of working days based on the requirements selected (AI51) ... =WORKDAY(TODAY(),AI51,AC53:AC163) ... AI51 = number of working days to add to Todays date and AC has the list of Holidays
that all works fine

what I need help with
IF the employee selects "Promotion" in cell Y41 ... I need the system to calculate: Todays date + the number that appears in AI51 and from that date select the next closest date (in the future) from a list of pre-determined dates located from AE53:AE120
IF the employee selects any other options in cell Y41 ... the calculation will be ... =WORKDAY(TODAY(),AI51,AC53:AC163)

so Todays date is 2024-07-11 .... AI51 = 15 which results in 2024-08-01 ... the nearest valid date, moving ahead, in the list (AE53:AE120) = 2024-08-15

Can someone help with a formula for that calculation?

thank you
Dan

 

[Rhodie72]

What is a "Valid Date"? Moving forward...

 

[Blanchetdb]

What is a "Valid Date"? Moving forward...

there is a list located in AE53:AE120 such as....

2024-08-15
2024-08-29
2024-09-12
2024-09-26
2024-10-10
2024-10-24
2024-11-07
etc... it goes all the way to 2030

so
If "Promotion" is selected in cell (Y41), then Today (2024-07-11) + the amount of working days in cell (AI51 .. for example 15) = the value in Cell (M37) = 2024-08-15
If "Transfer or any other option is selected in cell (Y41), the Today (2024-07-11) + the amount of working days in cell (AI51 .. for example 15) = the value in Cell (M37) = 2024-08-01

I presently have this =IF(Y41 = "Promotion", ____________, WORKDAY(TODAY(),AI51,AC53:AC163)) ...... its the blank part of the formula that I can't figure out