Help

Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here

Help with a conditional date formula f

Topic Labels: Formulas
Solved
Jump to Solution
578 3
cancel
Showing results for 
Search instead for 
Did you mean: 

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”)

image

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

1 Solution

Accepted Solutions
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.

See Solution in Thread

3 Replies 3
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?

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