Total Payment monthly breakdown based on Start and End Date Columns

Is the end date always the next month from the Start date? Can your date ranges span more than 2 month?

I could write a formula that calcuates the start date to the end of the month for the start date and the end date from the beginning of the month for the end date. But I'd need to think harder about how to accommodate the months inbetween start and end if the range is large.

If the range is never larger than adjacent months then I'll write up what I am thinking!

Kind regards

Debbie

Hi @Debbie Sawyer

Thank you for coming back to me, yes unfortunately the end date could span over multiple months.

Any support would be much appreciated

Thanks

Michael

Ok - leave it with me and I'll come back with a suggestion soon!

Kind regards

Debbie

Ok, so I have built it out for 2022 Jan - Apr

Here is a screen shot of the test data:

The Formula in Jan is:

=IF(AND(MONTH([Start Date]@row) = 1, MONTH([End Date]@row) = 1), ([End Date]@row - [Start Date]@row) + 1, IF(AND(MONTH([Start Date]@row) = 1, NOT(MONTH([End Date]@row) = 1)), ([Last Jan]# - [Start Date]@row) + 1, IF(AND(NOT(MONTH([Start Date]@row) = 1), MONTH([End Date]@row) = 1), ([End Date]@row - DATE(2022, 1, 1)) + 1, IF(AND(YEAR([Start Date]@row) < YEAR(DATE(2022, 1, 1)), MONTH([End Date]@row) > 1), ([Last Jan]# - DATE(2022, 1, 1)) + 1))))

The formula in Feb is:

=IF(AND(MONTH([Start Date]@row) = 2, MONTH([End Date]@row) = 2), ([End Date]@row - [Start Date]@row) + 1, IF(AND(MONTH([Start Date]@row) = 2, NOT(MONTH([End Date]@row) = 2)), ([Last Feb]# - [Start Date]@row) + 1, IF(AND(NOT(MONTH([Start Date]@row) = 2), MONTH([End Date]@row) = 2), ([End Date]@row - DATE(2022, 2, 1)) + 1, IF(AND(OR(MONTH([Start Date]@row) < 2, YEAR([Start Date]@row) < YEAR(DATE(2022, 1, 1))), MONTH([End Date]@row) > 2), ([Last Feb]# - DATE(2022, 2, 1)) + 1))))

This may seem a little long winded, but it does work! If you send me your email address, I'll share you to this example and you can have a closer look. I'd be more than happy to zoom with you and explain the workings and see if we can get it exactly right for you. I just didn't want to leave you waiting too long! 😁

Kind regards

Debbie

Just re-read your initial question and realised that you wanted the Jan £ totals!

Adjusted my example for that now too!

Jan formula now:

=(IF(AND(MONTH([Start Date]@row) = 1, MONTH([End Date]@row) = 1), ([End Date]@row - [Start Date]@row) + 1, IF(AND(MONTH([Start Date]@row) = 1, NOT(MONTH([End Date]@row) = 1)), ([Last Jan]# - [Start Date]@row) + 1, IF(AND(NOT(MONTH([Start Date]@row) = 1), MONTH([End Date]@row) = 1), ([End Date]@row - DATE(2022, 1, 1)) + 1, IF(AND(YEAR([Start Date]@row) < YEAR(DATE(2022, 1, 1)), OR(MONTH([End Date]@row) > 1), YEAR([End Date]@row) > YEAR(DATE(2022, 12, 31))), [Last Jan]# - DATE(2022, 1, 1) + 1)))) * [Day Rate]@row)

Feb Formula now:

=(IF(AND(MONTH([Start Date]@row) = 2, MONTH([End Date]@row) = 2), ([End Date]@row - [Start Date]@row) + 1, IF(AND(MONTH([Start Date]@row) = 2, NOT(MONTH([End Date]@row) = 2)), ([Last Feb]# - [Start Date]@row) + 1, IF(AND(NOT(MONTH([Start Date]@row) = 2), MONTH([End Date]@row) = 2), ([End Date]@row - DATE(2022, 2, 1)) + 1, IF(AND(OR(MONTH([Start Date]@row) < 2, YEAR([Start Date]@row) < YEAR(DATE(2022, 1, 1))), OR(MONTH([End Date]@row) > 2, YEAR([End Date]@row) > YEAR(DATE(2022, 12, 31)))), ([Last Feb]# - DATE(2022, 2, 1)) + 1)))) * [Day Rate]@row)

😃

I have been having fun! This will work for 2022 data as encompassing any start and end dates which use 2022 dates. (Start in 2021 and finish in 2022, or start in 2022 and finish in 2023) as long as the range is <365 days...

Hi @Debbie Sawyer

Thank you so much for this, you're a star! it all makes sense and have managed to add the other months in now.

Glad you had fun with this 😁 and thank you again!

Michael

Yay - glad it is working for you.

Like I mentioned above, this will fail if the date range spans so far that a month is within the range twice across 2 different years. But I am sure we could work this out somehow if it is a problem...

Kind regards

Debbie

HI @Debbie Sawyer

Hope you're well and thanks again with your support on the above. It worked fine creating each month in 2022, but when recreating for 2023 and beyond it doesn't seem to work as well, is there anything i'm doing wrong? I'm current amending the dates with year change only - is there more to it

Example - Jan 2023

=(IF(AND(MONTH([Start Date]@row) = 1, MONTH([End Date]@row) = 1), ([End Date]@row - [Start Date]@row) + 1, IF(AND(MONTH([Start Date]@row) = 1, NOT(MONTH([End Date]@row) = 1)), ([Last Jan]# - [Start Date]@row) + 1, IF(AND(NOT(MONTH([Start Date]@row) = 1), MONTH([End Date]@row) = 1), ([End Date]@row - DATE(2023, 1, 1)) + 1, IF(AND(YEAR([Start Date]@row) < YEAR(DATE(2023, 1, 1)), OR(MONTH([End Date]@row) > 1), YEAR([End Date]@row) > YEAR(DATE(2023, 12, 31))), [Last Jan]# - DATE(2023, 1, 1) + 1)))) * [Day Rate Adj]@row)

Here are some screenshots of whats happening

2022 - working as expected

2023 - when date range in same month (working as expected)

2023 - when date range is over 2months (jan and feb - issue with Jan)

Hi @Michael Stuart

Have you set up new Sheet Summary fields for the New Last Jan value (if you see what I mean!)

The Last Jan value in my example points to Jan 2022.

The formula you pasted in where Jan is failing is pointing to the Summary Field [Last Jan]# but does that field still have the old date in it?

Could this be the issue?

(Apologies, only had a quick glance before a meeting - can spend longer on this if this observation wasn't quite right)

Kind regards

Debbie