Calculating Due Date Based on 2 different criterias

Options
Syllica
Syllica ✭✭✭
edited 02/25/23 in Formulas and Functions

Hello!

I would like calculate the due date based on the date based on "Date Received" and "Type of Request". I have been doing this manually and it would be great to have some sort of automation by using a formula then that would be fantastic and save so much time. The challenge is creating the formula and I have not been successful.

For example, technical statements have a due date of 3 business dates. Whereas for a questionnaire then it would be 45 days (not business).

We have 5 different types of requests:

  • Technical statements - due in 3 business days from date received
  • Product questions - due in 5 business days from date received
  • Quality Agreements & Questionnaires - due in 45 days from date received, not business
  • Audits - due in 7-10 days from date received, not business

Not sure if it's possible to create a long formula that captures all of these or what is even possible.

Any help is appreciated and thank you in advance!


Tags:

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @Syllica

    Yes, this is very doable.

    This formula first checks that a date exists in your date received cell then evaluates what Type of Request is present.

    =IF(ISDATE([Date Received]@row), IF([Type of Request]@row = "Technical Statement", WORKDAY([Date Received]@row, 3), IF([Type of Request]@row = "Product Question", WORKDAY([Date Received]@row,5), IF(OR([Type of Request]@row = "Questionnaire", [Type of Request]@row = "Quality Agreement"), [Date Received]@row + 45, IF([Type of Request]@row = "Audit", [Date Received]@row + 7)))))

    Will this work for you?

    Kelly

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!