Invoice Date Calculator

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

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

Some notes:

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

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

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

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:

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