Help

Add a number to a date formula help?

Topic Labels: Formulas
2262 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Scott_Gardner1
7 - App Architect
7 - App Architect

Hello-

Trying add a field that can add days to my formula, any thoughts?

IF({DC Arrival Date}=BLANK(), BLANK(),
IF(OR({Production Type} = ‘Domestic’,{Production Type} = ‘Puzzles’),

DATETIME_FORMAT(DATEADD({DC Arrival Date},-56,‘Days’),‘MM/DD/YYYY’),

DATETIME_FORMAT(DATEADD({DC Arrival Date},-98,‘Days’),‘MM/DD/YYYY’)))

2 Replies 2

@Scott_Gardner1 - If I’ve got the logic of your formula correct, I think you can do a little simplification:

IF(
  {DC Arrival Date}, 
  IF(
    OR({Production Type} = 'Puzzles', {Production Type} = 'Domestic'),
    DATEADD({DC Arrival Date}, -56, 'days'), 
    DATEADD({DC Arrival Date}, -98, 'days')
  )
)

This says:

“if there is a DC Arrival Date, then if production type is Puzzles or Domestic, then add minus 56 days, but if not Puzzles or Domestic, then add minus 98 days”

What is implied in this formula is that if DC Arrival Date is blank then the resultaing date is blank.

This gives:

Screenshot 2022-11-22 at 21.31.21

I tend to use “if field has a value/is not null” rather than “if field is blank”. “if field has a value” is represented by:

IF(
  {DC Arrival Date}, 

To make the “blank” date value explicit you could put:

IF(
  {DC Arrival Date}, 
  IF(
    OR({Production Type} = 'Puzzles', {Production Type} = 'Domestic'),
    DATEADD({DC Arrival Date}, -56, 'days'), 
    DATEADD({DC Arrival Date}, -98, 'days')
  ),
  BLANK()
)

i.e. add the BLANK() on the last line of the IF, but this is not a required element of the formula, so generally I leave this out if my final value is blank/null/empty

Scott_Gardner1
7 - App Architect
7 - App Architect

Thank you very much!

I need to add some clarity, I am trying to create another field that is a number field, let’s call it “Add Lead Time”. When a number, let’s say 7 is added to this field, it will auto add 7 days to the formula which is a date field, make sense? Sorry if I wasn’t clear and thank you! And if there isn’t a number filled out in the Add Lead Time field it’s just blank but still has the date pulled from the DC date.