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:

Answers

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭
    Options

    @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
    Michael Stuart ✭✭✭
    Options

    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
    Debbie Sawyer ✭✭✭✭✭✭
    Options

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

    Kind regards

    Debbie

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

    @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
    Debbie Sawyer ✭✭✭✭✭✭
    edited 03/21/23
    Options

    @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
    Michael Stuart ✭✭✭
    Options

    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
    Debbie Sawyer ✭✭✭✭✭✭
    Options

    @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
    Michael Stuart ✭✭✭
    edited 06/13/23
    Options

    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)


  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭
    Options

    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

  • Michael Stuart
    Michael Stuart ✭✭✭
    Options

    Hi @Debbie Sawyer

    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!