Formula Help! Sum of multiple date range fields and amounts

Hi Jeremy

Thanks for responding.

I’ve been trying to define the date range in a formula field using a combination of IF, AND, IS BEFORE and IS AFTER. This is as far as I’ve got so far, but I know its wrong as it doesn’t work:

SUM(IF(AND(IS_AFTER({1st Payment Date}, ‘31/12/2019’),IS_BEFORE({1st Payment Date},‘31/01/2020’)), {1st Payment Amount})+IF(AND(IS_AFTER({2nd Payment Date}, ‘31/12/2019’),IS_BEFORE({2nd Payment Date},'31/01/2020’)),{2nd Payment Amount})+IF(AND(IS_AFTER({3rd Payment Date}, ‘31/12/2019’),IS_BEFORE({2nd Payment Date},‘31/01/2020’)),{3rd Payment Amount}))

Hopefully the formula explains what i’m trying to do better than the original email.

Cheers

Iain

Will that formula, as you have it written there, still be useful to you in 2021, or will you have to change the dates in the formula as time goes on?

Unless those dates are the relevant dates for the calculation you are wanting for all of time, I wouldn’t recommend putting the dates themselves into the formula like that.

Instead, you’ll probably want to have fields where you can define the date range for which you want to roll up the Payment amounts, so that you can change those dates as needed to view payment information across different time periods.

Does that make sense?

I would change the dates accordingly for 2021, the dates above are just an example.

I cant put date ranges (basically collecting expenditure across a month) in the each payment date field as their set payment dates, or am I misunderstanding what you are suggesting?

Cheers

Iain

Ok, @Iain_Rogers1,

So I’d suggest you make two formula fields that will calculate your “Begin Date” and “End Date” (or whatever you want to call them) for comparing, relative to the current year.

“Begin Date”

DATETIME_PARSE(
  '31/12/' & (YEAR(TODAY()) - 1),
  'DD/MM/YYYY'
)

“End Date”

DATETIME_PARSE(
  '31/01/' & YEAR(TODAY()),
  'DD/MM/YYYY'
)

That way you won’t have to adjust your formula every year - it will adjust itself on 1/1 of every year, and produce a date relative to the current year at that time.

Next, your formula was all good except one small thing - you separated your arguments inside the SUM() function with + rather than with commas - so, simple fix:

SUM(
  IF(
    AND(
      IS_AFTER({1st Payment Date}, {Begin Date}),
      IS_BEFORE({1st Payment Date},{End Date})
    ), 
    {1st Payment Amount}
  ), 
  IF(
    AND(
      IS_AFTER({2nd Payment Date}, {Begin Date}),
      IS_BEFORE({2nd Payment Date},{End Date})
    ),
    {2nd Payment Amount}
  ),
  IF(
    AND(
      IS_AFTER({3rd Payment Date}, {Begin Date}),
      IS_BEFORE({2nd Payment Date},{End Date})
    ),
    {3rd Payment Amount}
  )
)