Help

Setting automatic due dates based on different input dates

Topic Labels: Formulas
396 1
cancel
Showing results for 
Search instead for 
Did you mean: 
mrome1302
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi everyone,

I'm working in a table in which each item has two potential "availability" dates. Each item might have an availability date "A" and an availability date "B", but they will all have an availability date "B". I'm trying to set due dates for each item based on a few variables. What I need is a formula that compares availability dates A and B, and then gives me a due date that is either 60 days out or 5 days out. If availability date A is before date B or 55 days or less after date B, I need the due date to be based on date A and to be 60 days before date A. If date A is more than 55 days after date B or is empty, I need a formula that gives me a due date based on date B and one that is 5 days before date B. In the midst of all of that, I need a formula that won't give me weekends as a due date. 

Does anyone know if this is possible?

1 Reply 1
Sho
11 - Venus
11 - Venus

Hi @mrome1302 ,

Like this? I could be wrong.

IF({B},
  IF({A},
    IF({A}<=DATEADD({B},55,"Days"),
      DATEADD({A},-60,"Days"),
      DATEADD({B},-5,"Days")
    ),
    DATEADD({B},-5,"Days")
  )
)