Calculating Due Date Based on 2 different criterias

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 Answers

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

    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

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

    Hey @Jen_M

    Try this

    =IF(ISDATE([Communication Date]@row), IF(OR(Reason@row = "Initial Request for Information", Reason@row = "Missing Documentation", Reason@row = "Ineligibility Letter Sent", Reason@row = "Withdrawal letter Sent", Reason@row = "First level appeal denied"), WORKDAY([Communication Date]@row, 10), IF(OR(Reason@row = "Approved-Signing Event Required", Reason@row = "Post Completion Questionnaire"), WORKDAY([Communication Date]@row, 30), IF(Reason@row = "Non-Responsiveness", WORKDAY([Communication Date]@row, 14)))))

    Will this work for you?
    Kelly

Answers

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

    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

  • Syllica
    Syllica ✭✭✭

    Kelly, this is great! This solved the challenge I was having. Thank you very very much :)

  • Jen_M
    Jen_M ✭✭

    @Kelly Moore I am attempting to do something similar but when I replicate the above, I get #INVALID COLUMN VALUE as the return.

    I am looking at the Reason Column, and Communication Date, to assign a Response Due Date (currently testing in the DUE DATE TEST column in the screenshot below. The current Response Due Date is a simple date count formula and is what I am trying to eliminate.

    Initial Request for Information - 10 Days

    Missing Documentation - 10 Days

    Non-Responsiveness - 14 Days

    Ineligibility Letter Sent - 10 Days

    Withdrawal letter Sent - 10 Days

    First level appeal denied - 10 Days

    Approved-Signing Event Required - 30 Days

    Post Completion Questionnaire - 30 Days

    I tried testing all of my statuses like in your sample, and also just the first status listed above with your sample formula, to see if I could get it to work. Both yield #INVALID COLUMN VALUE. Can you assist and help me figure out what I am missing?

    Thank you in advance,

    Jennifer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    edited 06/29/24

    Hey @Jen_M

    Have you validated that your Due Date Test column is set up as a Date column? (Property type = Date)?

    Once we get this simple IF statement working properly, if desired, I can help you with the nested IF you will need to accommodate all the criteria you listed above.

    Kelly

  • Jen_M
    Jen_M ✭✭

    Hi @Kelly Moore thank you that resolved part of the issue. I was able to do the first part of the formula for the statuses that have a 10 day due date, but I can't get it to work once I add in the 14-day and 30-day statuses/due dates.

    Here are both of my attempts. Column DUE DATE TEST is where I tried all of the statuses with IF statements, DUE DATE Test 2, I did and IF(OR statement for the 10 day due date, but omitted the 14 and 30-day due dates as the formula doesn't work with them added. Would appreciate any insight into what I may be missing.

    The formula here works, but not when I add in the statuses with a 14-day and 30-day due date.

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

    Hey @Jen_M

    Try this

    =IF(ISDATE([Communication Date]@row), IF(OR(Reason@row = "Initial Request for Information", Reason@row = "Missing Documentation", Reason@row = "Ineligibility Letter Sent", Reason@row = "Withdrawal letter Sent", Reason@row = "First level appeal denied"), WORKDAY([Communication Date]@row, 10), IF(OR(Reason@row = "Approved-Signing Event Required", Reason@row = "Post Completion Questionnaire"), WORKDAY([Communication Date]@row, 30), IF(Reason@row = "Non-Responsiveness", WORKDAY([Communication Date]@row, 14)))))

    Will this work for you?
    Kelly

  • Jen_M
    Jen_M ✭✭

    @Kelly Moore that worked, thank you so much!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!