Formulas for + Days Date with Multiple Conditions and Date Countdown

JD_425

Picture1.png

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:

 

1. 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  
2. 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.   

@Paul Newcome

Paul Newcome

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))

Paul Newcome

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()))

JD_425

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

Paul Newcome

In that case you would use:

=[Date Submitted To DC]@row + IF([SMCC Action]@row = "NIR", 45, 14)

JD_425

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.

Paul Newcome

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))

JD_425

Perfect! Thank you so much Paul!

Paul Newcome

Happy to help. 👍️