Formula with date calculation

I community,
I need your help.

My customer can choose their date of delivery on my website.
I have a formula on Airtable who calculate the sending date depending of the carrier choose by the customer :
IF(Transporteur,
DATEADD({Date souhaitée par le client},
SWITCH(Transporteur, ‘GLS’, -2, ‘Chronopost’, -1),
‘days’)
)

{Date souhaitée par le client} : Delivery date
Transporteur : GLS or Chronopost (my 2 carriers)
-1 or -2 : calculation for "if the client ask for the 10th of december, the order has to be send the 8h if the client choose GLS.

Finally, the time depend of the day of the week end.
For exemple, if a cliente have a delivery date on Tuesday, i need to calcul -1 for GLS and not -2.
How can i conditionned that ?
Thank’s for your help

Matthieu

Hi Matthieu. What about putting another SWITCH statement in place of the -2 and the -1.

I think the formula below will handle the Tuesday delivery date case you described.

SWITCH(WEEKDAY({Date souhaitée par le client}),2,-1,-2)

Of course, you can add more values to the SWITCH to handle other days too. You would have a similar formula for the Chronopost part of the higher level SWITCH.

I haven’t tried any of this, but hope it helps.

1 Like