Skip to main content

Hi all,

I’m trying to create a formula to help me calculate invoicing periods.

If the payment date is before the 20th of the month the invoicing period is the current month, if the payment date is on or after, then the invoicing period will be the next month. Essentially, there should be two outputs, the first day of he current month or the first day of the next month.

I’ve tried to build this with an IF formula, but I’m coming unstuck with the first half of the formula when testing as it doesn’t seem to be calculating correctly:

IF({Date paid} < DATETIME_FORMAT({Date paid}, ‘20/MM/YYYY’), DATETIME_FORMAT({Date paid}, ‘01/MM/YYYY’), “test”)

How anyone advise on where I’m going wrong on this one?

IF(
   {Date Paid}, 
   DATEADD(
      DATETIME_PARSE(DATETIME_FORMAT({Date Paid}, "YYYY-MM")), 
      IF(DAY({Date Paid}) >= 20, 1, 0), 
      "month"
   )
)

^ the above formula checks if there is a value for the {Date Paid} field, then gets the first day of the month from the {Date Paid} field, then adds 1 month to that date but only if the {Date Paid} is the 20th-31st.

Thanks so much- that works, is there a way to format the date, so that it doesn’t show the time?

Thanks so much- that works, is there a way to format the date, so that it doesn’t show the time?


There’s a formatting tab when editing a field’s options.

Terms & ConditionsAccessibility statement