Formulas for + Days Date with Multiple Conditions and Date Countdown
I need some help creating a couple of formulas for + Days Date based on a couple of conditions and Date Countdown as would be greatly appreciated:
- In the SLA Due Date column if the SMCC Action column (has 2 drop down selections of NIR & LI) is NIR to populate a date + 45 days and if LI is selected to populate a date + 7 days
- In the Number of Days SLA Due column a date countdown formula to show the Netdays between the SLA Due Date column and the Date Submitted to DC and counts down the remaining days until a date is entered into the Actual Date Request Completed <DC Action> column and the countdown stops so we know many days were remaining upon completion.
Best Answer
-
First formula:
=IF([SMCC Action]@row = "NIR", [Date Submitted To DC]@row + 45, IF([SMCC Action]@row = "LI", [Date Submitted To DC]@row + 14))
Second Formula:
=NETDAYS(TODAY(), IF([Actual Date Request Completed <DC Action>]@row <> "", [Actual Date Request Completed <DC Action>]@row, [SLA Due Date]@row))
Answers
-
SLA Due Date column: What is the base date we are using? What date are we adding those days to?
Number of Days SLA Due column: You would use a formula similar to this:
=NETDAYS([Date Submitted To DC]@row, IF([Actual Date Request Completed <DC Action>]@row <> "", [Actual Date Request Completed <DC Action>]@row, TODAY()))
-
Hi Paul, Thank you!
The SLA Due Date Column date would be based on the Date in the Date Submitted to DC column with the below conditions:
If "NIR" is selected from the drop down in the SMCC Action column would be + 45
If "LI" is selected from the drop down in the SMCC Action column would be + 14
-
In that case you would use:
=[Date Submitted To DC]@row + IF([SMCC Action]@row = "NIR", 45, 14)
-
Perfect! Thank you Paul as greatly appreciated!
Sorry a follow up question: If we wanted to add additional drop down selection choices to the SMCC Action column (outside of the NIR or LI) but no SLA due date would need to be populated in the SLA Due Date Column for the additional drop down selections and would show as blank. It would only populate the date in the SLA Due Date Column if NIR or LI is chosen from the drop downs?
The second formula looking for the Number of Days SLA Due column:
If possible to create a date countdown formula to show the Netdays between the SLA Due Date column and the Date Submitted to DC (so for example NIR would start at 45 and then count down until a date is entered into the Actual Date Request Completed <DC Action> column signifying completed and the countdown stops so we know how many days were remaining when the the line item was completed.
-
First formula:
=IF([SMCC Action]@row = "NIR", [Date Submitted To DC]@row + 45, IF([SMCC Action]@row = "LI", [Date Submitted To DC]@row + 14))
Second Formula:
=NETDAYS(TODAY(), IF([Actual Date Request Completed <DC Action>]@row <> "", [Actual Date Request Completed <DC Action>]@row, [SLA Due Date]@row))
-
Perfect! Thank you so much Paul!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 146 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!