Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Nov 22, 2022 11:00 AM
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’)))
Nov 22, 2022 01:35 PM
@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:
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
Nov 22, 2022 01:49 PM
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.