Skip to main content
Solved

Estimated Delivery Date from todays date or date Payment is Received (nested if formula)

  • September 13, 2021
  • 4 replies
  • 55 views
I
Forum|alt.badge.img+3

4 columns to do this
Shipping Method - single select: boat, shipping
Payment Received - checkbox
Date of payment - date modified linked to payment received
Estimated Delivery Date - formula. I want to write an if statement.

This is what I have so far but I’m not sure how to write the second if statement
IF(Shipping=“boat”,DATEADD({Date Quoted},52,‘days’),DATEADD({Date Quoted},21,‘days’))

I would like to know how to write is so that is when payment is received estimate delivery is from payment date

if date modified is blank then from today boat +52days if flight +21days
if date modified is not blank then from date modified boat +52days if flight +21days

Best answer by Kamille_Parks11

The errors were caused (At least in part) due to using curly quotes within the formula and not straight quotes. In the forums, its best practice to wrap formulas in single ` or triple ```, which will force straight quotes.

In general, the formula can be simplified down quite a bit:

DATEADD(
   IF({Date of Payment}, {Date of Payment}, TODAY()),
   SWITCH({Shipping}, "boat", 52, "flight", 21),
   "days"
)

4 replies

Dimitris_Goudis
Forum|alt.badge.img+20

Hi Isaac,

nice logic problem to think. I recommend to use the AND and nested if checks.

However I have to mention that having a function in a field by default the field is not blank even if it seems blank. So to deal with that you can check the length of the field if it is above 1 means there is a date completed in the field, otherwise there are no values.

So you can use the following:

IF (AND(len(date_modified)<1, shipping method = “boat”),dateadd(TODAY(),52,‘days’),IF(AND(len(date_modified<1), shipping method = “flight”),dateadd(TODAY(),21,‘days’),IF(AND(len(date_modified>1), shipping method= “boat”),dateadd(date_modified,52,‘days’),dateadd(date_modified,21,‘days’))))

Please when you put the function on run be sure for the parenthesis. In case you face any trouble please do not hesitate to post it here :slightly_smiling_face:

I
Dimitris_Goudis
Forum|alt.badge.img+20

Hi can you copy and paste here the function please? You have missed some parenthesis.
I will copy it and correct it for you :winking_face:

I
Kamille_Parks11
Forum|alt.badge.img+27

The errors were caused (At least in part) due to using curly quotes within the formula and not straight quotes. In the forums, its best practice to wrap formulas in single ` or triple ```, which will force straight quotes.

In general, the formula can be simplified down quite a bit:

DATEADD(
   IF({Date of Payment}, {Date of Payment}, TODAY()),
   SWITCH({Shipping}, "boat", 52, "flight", 21),
   "days"
)
I
Dimitris_Goudis
Forum|alt.badge.img+20

The errors were caused (At least in part) due to using curly quotes within the formula and not straight quotes. In the forums, its best practice to wrap formulas in single ` or triple ```, which will force straight quotes.

In general, the formula can be simplified down quite a bit:

DATEADD(
   IF({Date of Payment}, {Date of Payment}, TODAY()),
   SWITCH({Shipping}, "boat", 52, "flight", 21),
   "days"
)

Hi @Kamille_Parks ,

This is really more efficient, I hade never used the switch function and your recommendation is really more efficient!

Thanks :slightly_smiling_face:

I
Terms & ConditionsAccessibility statement