How do I calculate the cost of a milestone, based on cost per workday and milestone start & end date

Alex Hackford

I am trying to calculate milestone cost based on a cost per day. The trick is, cost per day changes each month (based on financial periods), so if the milestones don't match up with the months start/end dates exactly (which they never do), I would need to include portions of multiple date ranges to sum the total milestone cost correctly.

I thought I could get to something like... "if the milestone start/end dates map to the start/end date of a month, multiply cost per day by the # of days in that milestone"...but I cannot for the life of me figure out how to structure this formula.

I've included the data I am currently working with. This is all on one sheet, the second section directly to the right of the first. Open to suggestions on how this data is organized too.

Help?!

Jason Albrecht

Hi @Alex Hackford

May I clarify your request with how I think the formula would be laid out?

• Check to see if the Milestone Start Date and Milestone End Date falls between the Month Start Date and Month End Date
• If so, return [Milestone Actual per day] * [Duration] (i.e., multiply the cost per day by the number of milestone days)
• If no,
  • count the number of (business?) days between the Milestone Start Date and the respective end-of-month
  • this is where I need your clarification please - what would be the calculation? Would you divide the {Actual Investment (pod cost) for that month} by the counted number of days?

I think I'm trying to understand which column of costs relates to "the cost per day [that] changes each month (based on financial periods)." Are the financial periods between the Month Start Date and the Month End Date?

Meanwhile, if the first two points is a correct understanding, you could check to see if the month of the Milestone Start Date and the Milestone End Date match. Something like:

=IF( MONTH( [Milestone Start Date]@row ) = MONTH( [Milestone End Date]@row ), [Milestone Actual per day]@row * [Duration]@row, "TBC" )

I'm assuming this formula would be placed next door to the column [Milestone Actual per day] and filter down each row, with a Total Sum at the end of the column. Is that what you were looking to do? Or did you have in mind something else?

Alex Hackford

Yes, thank you for translating my stream of consciousness. You are correct in the piece that needs clarifying. I would divide the {Actual Investment (pod cost) for that month} by the counted number of days.

Jason Albrecht

Hi @Alex Hackford

I utilised AI to come up with the following. Please note that I've not replicated and checked if this is right, but going through the formula it looks ok...

To calculate the Milestone Cost per day for a given row, you can use the following formula in a new column to the right of all this data:

=IF(AND(MONTH([Milestone Start Date]@row)=MONTH([Month Start Date]@row),MONTH([Milestone End Date]@row)=MONTH([Month End Date]@row)), [Milestone Actual per day]@row*[Duration]@row, IF(MONTH([Milestone Start Date]@row)=MONTH([Month Start Date]@row), ([Actual Investment (pod cost)]@row/NETWORKDAYS([Milestone Start Date]@row,EOMONTH([Milestone Start Date]@row,0)))*NETWORKDAYS([Milestone Start Date]@row,EOMONTH([Milestone Start Date]@row,0))+IF(MONTH([Milestone End Date]@row)=MONTH([Month End Date]@row), ([Actual Investment (pod cost)]@row/NETWORKDAYS(EOMONTH([Milestone End Date]@row,-1)+1,[Milestone End Date]@row))*NETWORKDAYS(EOMONTH([Milestone End Date]@row,-1)+1,[Milestone End Date]@row), ([Actual Investment (pod cost)]@row/NETWORKDAYS(EOMONTH([Milestone End Date]@row,-1)+1,EOMONTH([Milestone End Date]@row,0)))*NETWORKDAYS(EOMONTH([Milestone End Date]@row,-1)+1,EOMONTH([Milestone End Date]@row,0))))

In this formula, NETWORKDAYS is used to calculate the number of relevant network days between two dates. EOMONTH is used to calculate the end of the month for a given date. The formula checks if the Milestone Start and End Dates fall between the Month Start and End Dates. If they do, it multiplies the Milestone Actual per day by Duration. If not, it calculates the number of relevant network days from the Milestone Start and End Dates to the end of the month or start of the next month as required and then divides the Actual Investment (pod cost) by this number.

Using your example of ‘Estimate Delivery Date’, we can see that the Milestone Cost per day is $144355 as expected(*).

I hope this helps! Let me know if you have any other questions.

* Note: here is the prompting I used to have the AI produce the formula above. Please check that I've understood your request:

What we want to do is check to see if the Milestone Start Date and Milestone End Date falls between the Month Start Date and the Month End Date. if so, we want to calculate the Milestone Actual per day by Duration. However, if the Milestone Start Date starts in one month of Month Start Date but finishes in a later month in Month End Date, we want to work out the number of Networkdays from the Milestone Start Date to the end of that month and then take the relevant Actual Investment (pod cost) and divide it by the the number of Networkdays we just worked out, plus we then want to work out the number of Networkdays from the first of the next month and (making sure the Milestone End Date is in the same month) the Milestone End Date (if the Milestone End Date doesn't end in the next month, then we'll use all the Networkdays in that month), and having worked out the number of relevant Networkdays we want to again, divide the Actual Investment (pod cost) by this number. As an example, the first Milestone 'Estimate Delivery Date' has a Milestone Start Date of Aug-18-2023 and Milestone End Date of Nov-11-2023. For the sake of this exercise, we're saying the current date is Sep-11-2023, which is 17 network days from Aug-18-2023. We now want to calculate the Milestone Cost per day for this row (which will be in a new column to the right of all this data). We can work out that the Networkdays from Aug-18 to Aug-31 is 10. We can see the Actual Investment (pod cost) for Aug is $224,763 and the total [# of days in month] (or Networkdays) for Aug is 23. So we work out that ($224763/23)*10 = $97723. The networkdays from Sep-1 to Sep-11 is 7, so $139896/21*10=46632. By adding 97723 to 46632 we can say the Milestone Cost per day for the 'Estimate Delivery Date' Milestone is 144355. How would you write the Smartsheet formula to work this out?

Alex Hackford

This is super helpful and should work. Pasting the formula in as-is didn't work and I had to re-write it and am getting UNPARSEABLE. At first I thought it was b/c EOMONTH didn't work in Smartsheet but that seems to be false. Not sure if I'm missing a "()" or "," somewhere. Here's what a typed out manually, copying your AI generated formula above.

=IF(AND(MONTH([Milestone Start Date]@row)=MONTH([Month Start Date]@row), MONTH([Milestone End Date]@row)=MONTH([Month End Date]@row)), [Milestone Actual per day]@row * [Duration]@row, IF(MONTH([Milestone Start Date]@row)=MONTH([month start date]@row),([Actual Investment (pod cost)]@row/NETWORKDAYS([Milestone start date]@row, EOMONTH([Milestone start date]@row,0)))*NETWORKDAYS([Milestone start date]@row,EOMONTH([Milestone start date]@row,0))+IF(MONTH([Milestone end date]@row)=MONTH([Month end date]@row),([Actual investment (pod cost)]@row/NETWORKDAYS(EOMONTH([Milestone end date]@row, -1)+1,[Milestone end date]@row))*NETWORKDAYS(EOMONTH([Milestone end date]@row, -1)+1,[Milestone end date]@row), ([Actual Investment (pod cost)]@row/NETWORKDAYS(EOMONTH([Milestone end date]@row, -1)+1, EOMONTH([Milestone end date]@row,0)))*NETWORKDAYS(EOMONTH([Milestone end date]@row,-1)+1, EOMONTH([Milestone end date]@row,0)))))

Jason Albrecht

Hi @Alex Hackford

You're right. I had forgotten that Smartsheet does not currently have the EOMONTH function. I would suggest that you Submit a Product Enhancement Request when you have a moment and let @Genevieve P. know.

Meanwhile, there is a way around this. As @Paul Newcome has indicated in this post and elsewhere, we can use something like: IFERROR(DATE(YEAR([Date Column]@row), MONTH([Date Column]@row) + 1, 1), DATE(YEAR([Date Column]@row) + 1, 1, 1)) - 1

Given that you already have a column with all the end-of-month dates, there's probably a way of utilising that as well. If/when I get a chance, I'll look it to it a bit more, unless you or someone else in the community can beat me to the punch.

Thanks for the feedback by the way. It really helps.