VLOOKUP to calculate dates - holidays = due date

Chelsea Duncan

My Eval Pull date is based upon the TCP Type the user selects and also needs to calculate in the holidays.

Currently, Eval Start Date has a reference to another cell =[Date Completed]32

Once the Date Completed/Eval Start Date has a date, based upon the users pre-selected TCP Type of 115 days (VLOOKUP to TCPTypeDropDown and TCPTypeValue), an Eval pull date should be calculated off of that minus the holidays listed.

My current formula with the VLOOKUP in my Eval Pull Date is

The formula that I used when dealing with holidays, NOT using VLOOKUP, is

Can I merge the two and if so, how?

Thank you :)

Genevieve P.

Hi @Chelsea Duncan

Yes! Your current VLOOKUP formula can replace the Number Of Days portion of the WORKDAY formula.

WORKDAY( date, num_days, holidays )

So in your instance:

=IFERROR(WORKDAY([Eval Start Date]@row, VLOOKUP([TCP Type]@row, $TCPTypeDropDown$1:$TCPTypeValue$3, 2), Holidays:Holidays), "")

Note that I used @row instead of the row number after the column reference so it will run a little quicker (just looking in its own row for the values). I also added in $ signs to lock the VLOOKUP table reference to those specific cells, so you can drag-fill the formula down your column without it updating the row numbers.

Let me know if this works for you!

Cheers,

Genevieve

Genevieve P.

Hi @Chelsea Duncan

Yes! Your current VLOOKUP formula can replace the Number Of Days portion of the WORKDAY formula.

WORKDAY( date, num_days, holidays )

So in your instance:

=IFERROR(WORKDAY([Eval Start Date]@row, VLOOKUP([TCP Type]@row, $TCPTypeDropDown$1:$TCPTypeValue$3, 2), Holidays:Holidays), "")

Note that I used @row instead of the row number after the column reference so it will run a little quicker (just looking in its own row for the values). I also added in $ signs to lock the VLOOKUP table reference to those specific cells, so you can drag-fill the formula down your column without it updating the row numbers.

Let me know if this works for you!

Cheers,

Genevieve

Chelsea Duncan

Thank you! worked perfect! and thank you for explaining it :)

Genevieve P.

No problem at all! 🙂