Hi all,
I'm not sure if my aim should be achieved with a formula or an automation but I'm hoping someone can help me.
I have a various payment schedules for different companies which show me the dates each one is due to make a payment - I upload these into Airtable.
These payments may fall on a weekend. Where they do each company must make the payment either on the proceeding or preceding working day according to on what is stated in their T&Cs. I have a non-working day treatment field which indicates for each company whether it's meant to pay on the proceeding or preceding working day if the original payment due date is a weekend.
How can I make a formula that looks at the payment due date and if it falls on a weekend changes the due date (add or minus 1 or 2 days) according to the non-working day treatment field?
Alternatively, am I better off doing this in excel before I upload the payment schedule to Airtable?
Change dates that fall on a weekend to a pre/proceeding weekday according to another field
+21This is definitely something you can do in Airtable. Let’s use dueDate, beforeAfter, and payDate as field names. DueDate is the date it’s due, beforeAfter is the info from the T&Cs, payDate is the day it gets paid. Because I’m on mobile, this particular formula says says that Before payments happen on Thursday or Friday and After payments happen on Monday or Tuesday. Typing long formulas on mobile is the worst.
IF(AND(beforeAfter =“Before”, OR(WEEKDAY(dueDate)=6, WEEKDAY(dueDate)=0)), DATEADD(dueDate, -2, ‘days’), IF(AND(beforeAfter=“After”, OR(WEEKDAY(dueDate)=6, WEEKDAY(dueDate)=0)), DATEADD(dueDate, 2, ‘days’), dueDate))
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.