Calculating Due Date Based on 2 different criterias

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!

Find more posts tagged with

Formulas

Accepted answers

Kelly Moore

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

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

All comments

Kelly Moore

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.

Will this work for you?

Kelly

Syllica

https://community.smartsheet.com/discussion/comment/364447#Comment_364447

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

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

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

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

Hey @Jen_M

Try this

Will this work for you?
Kelly

Jen_M

@Kelly Moore that worked, thank you so much!

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.