# Help with a conditional date formula f

Topic Labels: Formulas
Solved
1858 3
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

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?

1 Solution

Accepted Solutions
16 - Uranus
``````IF(
{Date Paid},
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.

3 Replies 3
16 - Uranus
``````IF(
{Date Paid},
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.

5 - Automation Enthusiast

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

16 - Uranus

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