Help

Re: Help with a conditional date formula f

Solved
Jump to Solution
1474 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Bayo_Obasaju
5 - Automation Enthusiast
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”)

image

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

1 Solution

Accepted Solutions
Kamille_Parks
16 - Uranus
16 - Uranus
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
Kamille_Parks
16 - Uranus
16 - Uranus
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.

Bayo_Obasaju
5 - Automation Enthusiast
5 - Automation Enthusiast

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.