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!
Best Answers
-
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
-
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
-
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, this is great! This solved the challenge I was having. Thank you very very much :)
-
@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
-
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
-
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.
-
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 -
@Kelly Moore that worked, thank you so much!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
