Invoice Date Calculator

[FA_BK]

I’m trying to calculate an invoice due date based on the invoice date + terms. Because the payment terms of each client differs I am having some trouble. The fields I have are:
INV 1 DATE (Date Field Type, Friendly Format)
TERMS (LookUp Field Type from the Clients table, entries include 30, 45, EOM + 45)
INV 1 DUE (Formula Field Type)

I have tried IF formulas and the following works:
IF({TERMS}=30, DATEADD({INV 1 DATE},30,‘day’), DATEADD({INV 1 DATE},45,‘day’))

I can’t figure out how to add the EOM+45 as a Nested IF. Would love your help. Thank you in advance.

[JonathanBowen]

Hi @FA_BK - this is actually pretty tricky to do as, in the EOM + 45 scenario you need to push the due date into the next month, but if the invoice date is in December you need to push it into the next month and the next year, so my first attempt of trying to parse the date into the day, month and year elements got way too complicated. I think this is a solution though:

In this scenario I think a SWITCH formula is easier than a nested IF (check the docs here:
https://support.airtable.com/hc/en-us/articles/203255215-Formula-Field-Reference#logical)

So, your client is a link and the terms are a lookup (as you have). Then add 31 days to every invoice date (this will only come into play on the EOM + 45):

DATEADD({INV 1 DATE}, 31, 'days')

Then find the first of the month for the date in {Add 31}

DATETIME_FORMAT(DATEADD({INV 1 DATE}, 31, 'days'), '01-MM-YYYY')

Then, finally, use the SWITCH formula to figure out the due date:

SWITCH(
TERMS, 
'30', DATEADD({INV 1 DATE}, 30, 'days'), 
'45', DATEADD({INV 1 DATE}, 45, 'days'), 
'EOM + 45', DATEADD(DATETIME_PARSE({1st of month}, 'DD-MM-YYYY'), 44, 'days') 
)

Because it is much more complex to work out the last day of any given month than the 1st day of the next month, this is technically “1st day of next month, plus 44” rather than “end of current month, plus 45” but the results are the same.

Hope this does the job!

JB

[FA_BK]

@JonathanBowen YOU ARE AMAZING. Thank you. I tried so many things. Thank you thank you thank you, it works! It’s going to save us so much time. I really appreciate it.

[Elias_Gomez_Sai]

Some notes:

JonathanBowen:

DATEADD({INV 1 DATE}, 31, 'days')

If you want to add one month, use better ‘month’ as the unit, not all months have 31 days.

JonathanBowen:

DATETIME_FORMAT(DATEADD({INV 1 DATE}, 31, 'days'), '01-MM-YYYY')

Why you use DATETIME_FORMAT instead of just leaving it as date? Then you can remove DATETIME_PARSE in the SWITCH.

[JonathanBowen]

Elias_Gomez_Sainz:

If you want to add one month, use better ‘month’ as the unit, not all months have 31 days.

Yes, I was thinking that + 31 days always gets me into the next month, but never into the month after, then I’m resetting the DD element to 01. So, I think this works both ways (days and months), yes?

The DATETIME_FORMAT was to force the Date to “01” then in the final step adding 44 days to it (and as it is a string at this point, I need DATETIME_PARSE first).

JB

[Elias_Gomez_Sai]

JonathanBowen:

So, I think this works both ways (days and months), yes?

What do you mean? :thinking: If you add 31 days to January 29, you’ll end in March instead of February. If you add 1 month, Airtable fixes it and gives you the last day:

JonathanBowen:

to force the Date to “01”

Oh yes, I missed the ‘01’ :man_facepalming: . Of course to find the first day that’s needed haha

[JonathanBowen]

Elias_Gomez_Sainz:

What do you mean? :thinking: If you add 31 days to January 29, you’ll end in March instead of February. If you add 1 month, Airtable fixes it and gives you the last day:

Ah, yes, you’re right. I used Feb as my “minimum” test, but Jan -> Feb is the one I need. Good spot!

@FA_BK - A slight amendment you’ll need to add in:

Add 31 is:
DATEADD({INV 1 DATE}, 31, 'days')

But should be:

Add 1 Month
DATEADD({INV 1 DATE}, 1, 'month')

JB