Total Payment monthly breakdown based on Start and End Date Columns

Michael Stuart · 03/17/23

Hi Everyone,

I need some support with a formula, if anyone can help me 😁

I have the below sheet where I have created costing for resource use between a Start and End Date Column supported by a total day column count between the two dates and use a day rate column to multiply the total column.

Screenshot 2023-03-17 at 14.02.57.png

What I would like now to do, is create 12 columns for each month to break down the total over the months covered by the start date.

For Example

Start Date: 15/01/2023

End Date: 5/02/2023

Day Rate: £100

Total: £2100

I would like to break that down further to

January: 16 Days x £100 = £1600 (Column Value)

February: 5 Days x £100 = £500 (Column Value)

Any Help would be much appreciated? If you need any further info please let me know.

Debbie Sawyer · 03/20/23

@Michael Stuart

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

Michael Stuart · 03/21/23

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

Debbie Sawyer · 03/21/23

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

Kind regards

Debbie

Debbie Sawyer · 03/21/23

@Michael Stuart

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

Debbie Sawyer · 03/21/23

@Michael Stuart

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...

Michael Stuart · 03/22/23

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

Debbie Sawyer · 03/22/23

@Michael Stuart

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

Michael Stuart · 06/13/23

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