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


image.png


Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
«1

Answers

  • Paul Newcome
    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
    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?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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
    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
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
  • 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
    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!!!

    image.png


  • Paul Newcome
    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)))

@BStump<\/a> <\/p>

This method does produce separate reports:<\/p>

The Project Plan report will include this filter: \"Sheet Type\" is one of \"Project Plan\" -- this filter will exclude rows coming from the RAID Log (and all other types of sheets) because those other sheets will have a value in their \"Sheet Type\" column that is not \"Project Plan.\" <\/p>

The RAID Log report will include this filter: \"Sheet Type\" is one of \"RAID Log\" -- this filter will exclude rows coming from the Project Plan (and all other types of sheets) because those other sheets will have a value in their \"Sheet Type\" column that is not \"RAID Log.\" <\/p>

Thus, each report will include a filter rule specifying that only rows with its own sheet type can be included.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":265,"urlcode":"Reports","name":"Reports"}]},{"discussionID":108415,"type":"question","name":"Creating Check out\/in sheet and form?","excerpt":"Is it possible to create a check out\/in sheet and form where the entries from the form will land on the same row? I want to create a form where people can check things out\/in on the same date. So I'd check things out the in the morning of 8\/2 and check them in at closing time. I want all the data to land on the same row so…","snippet":"Is it possible to create a check out\/in sheet and form where the entries from the form will land on the same row? I want to create a form where people can check things out\/in on…","categoryID":321,"dateInserted":"2023-08-02T13:59:28+00:00","dateUpdated":null,"dateLastComment":"2023-08-02T14:53:22+00:00","insertUserID":155551,"insertUser":{"userID":155551,"name":"mistone","url":"https:\/\/community.smartsheet.com\/profile\/mistone","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-02T15:19:06+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":126800,"lastUser":{"userID":126800,"name":"Kelly P.","url":"https:\/\/community.smartsheet.com\/profile\/Kelly%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-02T16:17:14+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":14,"score":null,"hot":3381973370,"url":"https:\/\/community.smartsheet.com\/discussion\/108415\/creating-check-out-in-sheet-and-form","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108415\/creating-check-out-in-sheet-and-form","format":"Rich","lastPost":{"discussionID":108415,"commentID":388490,"name":"Re: Creating Check out\/in sheet and form?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/388490#Comment_388490","dateInserted":"2023-08-02T14:53:22+00:00","insertUserID":126800,"insertUser":{"userID":126800,"name":"Kelly P.","url":"https:\/\/community.smartsheet.com\/profile\/Kelly%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-02T16:17:14+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-02T15:19:19+00:00","dateAnswered":"2023-08-02T14:53:22+00:00","acceptedAnswers":[{"commentID":388490,"body":"

@mistone<\/a> <\/p>

Form entries cannot land in the same row. However, you could use a form entry to check out and then use an update request to check in. <\/p>

Hope this helps!<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108381,"type":"question","name":"SCC Blueprint - Customize Profile Data","excerpt":"Hello Smartsheet Community, I am working on creating a Blueprint in Smartsheet's Control Center. Would someone be able to help me understand the differences between the \"Cell Link\", \"One Time Write\", and \"Editable Value\" options when on the customizing profile data page in my Blueprint settings? I haven't been able to find…","snippet":"Hello Smartsheet Community, I am working on creating a Blueprint in Smartsheet's Control Center. Would someone be able to help me understand the differences between the \"Cell…","categoryID":321,"dateInserted":"2023-08-01T20:45:57+00:00","dateUpdated":"2023-08-02T11:44:42+00:00","dateLastComment":"2023-08-02T16:12:15+00:00","insertUserID":159822,"insertUser":{"userID":159822,"name":"RyanF","url":"https:\/\/community.smartsheet.com\/profile\/RyanF","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/XGSHEPORDR99\/n8IO7FYKW8JCE.jpg","dateLastActive":"2023-08-02T18:37:37+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":91566,"lastUserID":159822,"lastUser":{"userID":159822,"name":"RyanF","url":"https:\/\/community.smartsheet.com\/profile\/RyanF","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/XGSHEPORDR99\/n8IO7FYKW8JCE.jpg","dateLastActive":"2023-08-02T18:37:37+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":25,"score":null,"hot":3381916692,"url":"https:\/\/community.smartsheet.com\/discussion\/108381\/scc-blueprint-customize-profile-data","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108381\/scc-blueprint-customize-profile-data","format":"Rich","tagIDs":[466,579],"lastPost":{"discussionID":108381,"commentID":388528,"name":"Re: SCC Blueprint - Customize Profile Data","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/388528#Comment_388528","dateInserted":"2023-08-02T16:12:15+00:00","insertUserID":159822,"insertUser":{"userID":159822,"name":"RyanF","url":"https:\/\/community.smartsheet.com\/profile\/RyanF","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/XGSHEPORDR99\/n8IO7FYKW8JCE.jpg","dateLastActive":"2023-08-02T18:37:37+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-02T16:12:18+00:00","dateAnswered":"2023-08-02T13:41:58+00:00","acceptedAnswers":[{"commentID":388472,"body":"

Sure thing Ryan. I will try to explain it as best as I can, but if you still have questions let me know.<\/p>

Cell Link<\/strong> - Links the cells together so that any time the source changes, the destination will also be updated. This is useful for keeping information updated throughout the life of the project as things change<\/p>

One Time Write<\/strong> - Exactly what it says it is. The information will be written into the cell one time, and no updates will occur as data changes on the source sheet<\/p>

Editable Value<\/strong> - People deploying projects from the blueprint will be able to edit the value for this field when setting up the project<\/p>

I hope that helps.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":466,"urlcode":"control-center","name":"Control Center"},{"tagID":579,"urlcode":"blueprint","name":"blueprint"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=321&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":4906,"limit":3},"title":"Trending in Smartsheet Basics","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Smartsheet Basics