How to spread costs evenly across months, if they fall within those dates
I have various financial line items that span different dates. I'd like the costs to be spread per month automatically based on the date and cost entry.
I've tried various functions already, IF, AND, and it says Unpearsable.
I'd like to spread out the costs in the Cost column to the months on the right, based on the start and end date entered. Also, if a cost shows up in the month cell, I'd like the cell color to change (can do this with conditional formatting once the cost shows up).
Really appreciate the help!!
Best Answers
-
Paul Newcome ✭✭✭✭✭✭You would use the below for Jan 2023 and adjust the month and year numbers accordingly for each of the other months.
=IF(AND(MONTH([Start Date]@row)<= 1, YEAR([Start Date]@row)<= 2023, MONTH([End Date]@row)>= 1, YEAR([End Date]@row)>= 2023), [$ per month]@row)
-
Paul Newcome ✭✭✭✭✭✭
Lets try a different approach.
=IF(AND(VALUE(YEAR([Start Date]@row) + IF(MONTH(Start Date]@row)< 10, "0", "") + MONTH([Start Date]@row))<=202307, VALUE(YEAR([End Date]@row) + IF(MONTH([End Date]@row)< 10, "0", "") + MONTH([End Date]@row))>=202307), [$ per month]@row)
Basically we are creating a yyyymm stamp from the start and end dates and comparing them to the yyyymm stamp for that year/month combo. The above is for July 2023 (202307).
-
Paul Newcome ✭✭✭✭✭✭
Answers
-
Paul Newcome ✭✭✭✭✭✭
There are already a number of threads out here in the Community detailing how to do this. I'll see if I can find some links for you.
-
Thanks Paul! Yeah I looked through a lot of those, but kept getting errors. Figured this set up was a bit different... Let me look again.
-
Paul Newcome ✭✭✭✭✭✭
我不能to find any of the posts even though I specifically remember helping someone with this exact thing just a few weeks ago.
Are you overlapping into a new year in your sheet, or is it strictly 2023?
-
It will go into 2024...
-
Paul Newcome ✭✭✭✭✭✭
You would use the below for Jan 2023 and adjust the month and year numbers accordingly for each of the other months.
=IF(AND(MONTH([Start Date]@row)<= 1, YEAR([Start Date]@row)<= 2023, MONTH([End Date]@row)>= 1, YEAR([End Date]@row)>= 2023), [$ per month]@row)
-
Worked perfectly!! THank you SOOOOO much!!! I spent too much time trying to get that formula right :)
-
Paul Newcome ✭✭✭✭✭✭
-
再次你好! !我有一个跟进的问题。This works perfectly in 2023, but when I move to 2024 columns and use months 1, 2, 3 etc and year 2024, data does not show up. Any reason that formula needs to be different?
-
Paul Newcome ✭✭✭✭✭✭
Lets try a different approach.
=IF(AND(VALUE(YEAR([Start Date]@row) + IF(MONTH(Start Date]@row)< 10, "0", "") + MONTH([Start Date]@row))<=202307, VALUE(YEAR([End Date]@row) + IF(MONTH([End Date]@row)< 10, "0", "") + MONTH([End Date]@row))>=202307), [$ per month]@row)
Basically we are creating a yyyymm stamp from the start and end dates and comparing them to the yyyymm stamp for that year/month combo. The above is for July 2023 (202307).
-
I think I know why, but dont know how to fix it. Let's say there's an activity that starts Jan 2023 and extends till Apr 2024. The cost is spreading as expected up to April 2023 and then stop, maybe because the formula is getting confused between Month 4 in 2023 and 2024. But I don't know how to fix it. Here are my formulae. If someone can tell me what I'm doing wrong, that would be really appreciated!
Month months in 2023: =IF(AND(MONTH([Start Date]@row) <= 1, YEAR([Start Date]@row) <= 2023, MONTH([End Date]@row) >= 1, YEAR([End Date]@row) >= 2023), [$ per Month]@row)
- I adjust to month 2 and 3 etc for 2023
For 2024: IF(AND(MONTH([Start Date]@row) <= 1, YEAR([Start Date]@row) <= 2024, MONTH([End Date]@row) >= 1, YEAR([End Date]@row) >= 2024), [$ per Month]@row)
The problem is, when the month numbers start overlapping (between 2023 and 2024, as in the row extends from say April 2023 to Jun 2023), then any months that repeat in both years dont get a value.
Any ideas? THank you!!!
-
Paul Newcome ✭✭✭✭✭✭
-
The updated formula you sent does seem to work for 2024! Thank you... these formulae just arent intuitive to me, though I do love to use Smartsheets!
-
Paul Newcome ✭✭✭✭✭✭
-
Hi Paul,
Sorry!!! One more question - if my time window is say 4/1/23 to 7/15/23, it is splitting evenly over 4 months (making the total higher than the actual cost). In the month of July, the cost spread should only be 50%, of that month since the line item is only till Jul 15th. I added in $ per day as below.
THis is my April formula below:
=IF(AND(VALUE(YEAR([Start Date]@row) + IF(MONTH([Start Date]@row) < 10, "0", "") + MONTH([Start Date]@row)) <= 202304, VALUE(YEAR([End Date]@row) + IF(MONTH([End Date]@row) < 10, "0", "") + MONTH([End Date]@row)) >= 202304), [$ per Month]@row)
I've tried a few different things to change that last "[$ per Month]@row" into total cost of number of days in that particular month, but everything is giving me an error.
What do you suggest? THANK YOU! You're the best!!!
-
Paul Newcome ✭✭✭✭✭✭
That definitely adds some complexity to it. Give this a try:
=IF(VALUE(YEAR([Start Date]@row) + IF(MONTH([Start Date]@row) < 10, "0", "") + MONTH([Start Date]@row)) = 202304, ((DATE(YEAR([Start Date]@row) + IF(MONTH([Start Date]@row) = 12, 1, 0), MONTH([Start Date]@row) + IF(MONTH([Start Date]@row) = 12, -11, 1), 1) - 1) - [Start Date]@row) * [$ per day]@row, IF(VALUE(YEAR([End Date]@row) + IF(MONTH([End Date]@row) < 10, "0", "") + MONTH([End Date]@row)) = 202304, DAY([End Date]@row) * [$ per day]@row, IF(AND(VALUE(YEAR([Start Date]@row) + IF(MONTH([Start Date]@row) < 10, "0", "") + MONTH([Start Date]@row)) <= 202304, VALUE(YEAR([End Date]@row) + IF(MONTH([End Date]@row) < 10, "0", "") + MONTH([End Date]@row)) >= 202304), [$ per Month]@row)))
