VLOOKUP to calculate dates - holidays = due date

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 :)

Find more posts tagged with

Accepted answers

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

All comments

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), "")

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! 🙂

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.