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
-
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)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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).
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@AshwiniBiotech2023 Check out my last comment.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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.
-
I can't seem 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?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
It will go into 2024...
-
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)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Worked perfectly!! THank you SOOOOO much!!! I spent too much time trying to get that formula right :)
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Hello again there!! I had one follow up question. 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?
-
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).
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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!!!
-
@AshwiniBiotech2023 Check out my last comment.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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!
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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!!!
-
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)))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives