I am struggling with a date formula:
I want to calculate the number of weeks that a record will be early or late based on three variables. The Delivery Date, The Ex Factory Date and the Freight method.
The formula below is the equivalent of what i would use in excel. I calculate it in days and then divide by 7 to show the number of weeks. The formula doesn’t seem to work in Airtable and i cant work out why this is. I was wondering if anyone would be able to give me some advice on this?
=IF(OR(“Freight” = “Sea”; Freight =“Air”);IF(“Ex Fact” >0;”DC Date” - “Ex Fact” - 49)/7; IF(“Ex Fact”>0;”DC Date” - “Ex Fact” -10 )/7)
In words this is what i’m trying to calculate is the following -
if the freight method is “Sea” we add 49 days (7 weeks) to the Ex Factory date
I want to know the difference between this date and the planned DC date.
If the freight method is “Air” we add 10 days to the ex factory date. We want to know the different between this date and the planned DC date.