# Total Payment monthly breakdown based on Start and End Date Columns

Options

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.

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.

Tags:

• ✭✭✭✭✭✭
Options

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

• Options

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

• ✭✭✭✭✭✭
Options

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

Kind regards

Debbie

• ✭✭✭✭✭✭
edited 03/21/23
Options

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

• ✭✭✭✭✭✭
edited 03/21/23
Options

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

• Options

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

Michael

• ✭✭✭✭✭✭
Options

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

• edited 06/13/23
Options

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)

• ✭✭✭✭✭✭
Options

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

• Options

Doh!!! a proper Homer Simpson moment from me, totally forgot about the sheet summary element. Thanks so much for your help again!!

Thanks

Michael

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!