Formula Help! Sum of multiple date range fields and amounts

[Iain_Rogers1]

Hi there, Im looking for a bit of help with a formula. I’ll try and explain it as best I can…

I have six fields as follows:

• 1st Payment Date (date type) (e.g 1st Jan 2020)

• 1st Payment Amount (Currency) (e.g £500)

• 2nd Payment Date (date type) (e.g 3rd Jan 2020)

• 2nd Payment Amount (Currency) (e.g £500)

• 3rd Payment Date (date type) (e.g 5th Jan 2020)

• 3rd Payment Amount (Currency) (e.g £500)

What I would like to do is create another field with a formula that would basically sum all the payment dates which fit within a specific date range (an example date range relating to the fields above would be the 1st to the 31st Jan 2020)

Would anyone be able to help with this?

Thanks in advance!

[Jeremy_Oglesby]

Hi @Iain_Rogers1

Where is the date range for this being defined? Do you have date fields where you are entering the date range you want to sum for?

[Iain_Rogers1]

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

[Jeremy_Oglesby]

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?

[Iain_Rogers1]

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

[Jeremy_Oglesby]

When you change the dates for 2021, would you be changing them to:

• After 31/12/2020
• Before 31/01/2021

In other words, are the “Day of the Year” dates always the same, and it’s just the “Year” portion that changes each year?

[Iain_Rogers1]

Yes thats correct.

Cheers!

[Jeremy_Oglesby · Accepted solution]

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

[Iain_Rogers1]

Hi Jeremy

Thats worked great! Thanks very much!

Cheers

Iain