Calculating costs for a task over more than one month

Lloyd Vasquez

I'm trying to come up with a formula to calculate the labor costs per month in one sheet ( 12 columns, one for each month), by referencing another sheet, and referring to the start/finish columns of said task. E.g. Task 1: Start = Aug 27, Finish = Sept 5. So in my cost sheet, the formula needs to refer to the start date and determine how many days of costs it can see for the rest of that month. In this example, there would be 5 days for Aug. In my Sept cost column, I would use a similar formula, but it would evaluate the dates to start at the beginning of (sept) the month and know that there is also 5 days (finish day = Sept 5) of costs in Sept. I want to see the 5 days of costs in each respective month returned to my cost sheet..(cost not count).

If the schedule changes to Aug 30 to Sept 8, I would like to see the cost change accordingly in the cost sheet, i.e. 2 days of costs in Aug and 8 days of costs in Sept.

Breakout:

1) I had been using SUMIFS to confirm criteria for a similar formula (e.g. =SUMIFS({Materials Range 1}, {Materials Range 2}, $[Project ID]$10, {Materials Range 3}, Phase81, {Materials Range 4}, MONTH(@cell) = 1). This returns all the costs for project x, phase y that occur January to my cost sheet based on a date column on the materials sheet.

sumIFs - project # = X (references range in scheduling sheet, as there can be more than one task that can apply to the calc)

sumIFs - phase # = Y (references range in scheduling sheet, as there can be more than one task that can apply to the calc)

2) This is the struggle, I need smartsheet to logically know to find how many days left in the month of the start date to calculate cost based on that many days left, in addition to the spill over into the following month (cost calculation in different column (month) of calc sheet)

a) Sum (Avg daily rate * Total # of days between (start date) and Endofmonth(start date)

b) Sum (Avg daily rate * Total # of days between beginningofmonth (finish date) and (finish date)

I think I need to create dummy columns for these calcs, then continue with my previous method of SUMIFS for the appropriate range as it applies to the correct month in my cost sheet.

Is this possible ?

Kelly Drake

The formulas below add 12 Sheet Summary date fields for the 1st of every month to your sheet and then you can use this formula in all of your date columns so that you can just update the 12 cells instead of having to update the formula.....

IF(MONTH([Start Date]@row) = 1, [February]# - [Start Date]@row, IF(MONTH([Start Date]@row) = 2, [March]# - [Start Date]@row, IF(MONTH([Start Date]@row) = 3, [April]# - [Start Date]@row, IF(MONTH([Start Date]@row) = 4, [May]# - [Start Date]@row, IF(MONTH([Start Date]@row) = 5, [June]# - [Start Date]@row, IF(MONTH([Start Date]@row) = 6, [July]# - [Start Date]@row, IF(MONTH([Start Date]@row) = 7, [August]# - [Start Date]@row, IF(MONTH([Start Date]@row) = 8, [September]# - [Start Date]@row, IF(MONTH([Start Date]@row) = 9, [October]# - [Start Date]@row, IF(MONTH([Start Date]@row) = 10, [November]# - [Start Date]@row, IF(MONTH([Start Date]@row) = 11, [December]# - [Start Date]@row, IF(MONTH([Start Date]@row) = 12, [January]# - [Start Date]@row, 0)))))))))))) + IF(MONTH([End Date]@row)=1, ([End Date]@row - [January]#+1), IF(MONTH([End Date]@row)=2, ([End Date]@row - [February]#+1), IF(MONTH([End Date]@row)=3, ([End Date]@row - [March]#+1), IF(MONTH([End Date]@row)=4, ([End Date]@row - [April]#+1), IF(MONTH([End Date]@row)=5, ([End Date]@row - [May]#+1), IF(MONTH([End Date]@row)=6, ([End Date]@row - [June]#+1), IF(MONTH([End Date]@row)=7, ([End Date]@row - [July]#+1), IF(MONTH([End Date]@row)=8, ([End Date]@row - [August]#+1), IF(MONTH([End Date]@row)=9, ([End Date]@row - [September]#+1), IF(MONTH([End Date]@row)=10, ([End Date]@row - [October]#+1), IF(MONTH([End Date]@row)=11, ([End Date]@row - [November]#+1),IF(MONTH([End Date]@row)=12, ([End Date]@row - [December]#+1), 0))))))))))))

You need to do the +1 because the subtraction doesn't count the 1st of hte month and it should.

Things to consider with the above..... Do projects ever span more than 2 months where you would need to count entire months? If yes, this could greatly complicate the calculations. Also how long will you be using what you're setting up? Finally, this formula counts every day - not just weekdays/work days.

Paul Newcome

Will it only ever fall into two months, or is there a possibility of say starting in June but ending in August so that you would have a few days in June, every day in July, and a few days in August?