I need a formula to calculate projected hours based on start/finish dates

Bobbie P

In a project plan we have start date, finish date, and an estimated hours column to track against budgets.

I need to be able to identify and total projected hours needed by month.

Formula should return and absolute 36 hours for December - populating 20 and 16 in Dec column, 28 hours for January - populating 8 and 20 hours in Jan column, and some way identify a split for Task 3 hours across Dec/Jan (if that can't be done those tasks will have to be manually calculated - BUT ask for the moon and hope to land in the stars, right?)

I have created a test project plan and published with this link: https://app.smartsheet.com/b/publish?EQBCT=4f136586f0584577abff31ec15702037

Does anyone know how I would go about this? Any guidance would be GREATLY appreciated!

Paul Newcome

Ok. Lets start with the easy one first and get that together. Then we can move onto the more complicated portions (identifying which column to output in and the parts for spanning multiple months).

IF the start and finish are within the same month and year, then output Estimated Hours.

=IF(AND(MONTH(Start@row) = MONTH(Finish@row), YEAR(Start@row) = YEAR(Finish@row)), [Estimated hours]@row)

Paul Newcome

You will need to tweak the formulas as you go across the columns for the different months, but here is the logic...

You need to first determine how many days there are in December. If both start and finish dates are in December, then you can subtract one from the other. If they are in a different month, then you would subtract the date from the last day in December.

Which leads me to a question before we get too far into it... Is it possible for a task to span more than two months (December to February for example)?

Bobbie P

Thank you so much for your response Paul!

Yes, it is possible for a task to span more than two months (like PM hours across the entire project), but my assumption is those would need to be managed with a different formula that would be the same for every column (total hours divided by number of total project months).

I still don't know how I would create the formula... my initial thought was to identify each column's date range, but I don't know how to have it return the value in Estimated Hours column.

For example, December column would identify the IF, but I just don't know how to get the THEN:

=IF(Start@row > 12 / 1 / 21, Finish@row < 12 / 31 / 21), how do I get the value in Estimated Hours column to copy here?

Paul Newcome

Ok. Lets start with the easy one first and get that together. Then we can move onto the more complicated portions (identifying which column to output in and the parts for spanning multiple months).

IF the start and finish are within the same month and year, then output Estimated Hours.

=IF(AND(MONTH(Start@row) = MONTH(Finish@row), YEAR(Start@row) = YEAR(Finish@row)), [Estimated hours]@row)

Bobbie P

Thank you Paul. I was able to get Dec and Jan formulas to work

=IF(AND(Start@row >= DATE(2021, 12, 1), Finish@row <= DATE(2021, 12, 31)), [Estimated Hours]@row)

=IF(AND(Start@row >= DATE(2022, 1, 1), Finish@row <= DATE(2022, 1, 31)), [Estimated Hours]@row)

I also managed to create a formula to take Task 3 which spans 2 months and split the time between the two... not incredibly accurate and I still may elect to manually adjust those but at least I have direction now.

IF(AND(Start@row >= DATE(2021, 12, 1), Finish@row <= DATE(2022, 1, 31)), [Estimated Hours]@row / 2)

I don't know if this is the most efficient (or elegant) way to calculate projected hours by month, but THANK YOU so much for your assistance and guidance as I was completely stuck!!

Paul Newcome

I had every intention of helping you through this entire solution. I just wanted to take it one step at a time to make sure each bit was working as expected. We actually do not want to add in the part for determining month yet as we are going to build on what we already have.

We want to keep the AND portion comparing months, because eventually that is going to be a part of a nested IF that should hopefully account for every possible scenario.

The bit for determining which column to output the totals in would actually be more like this...

=IF(AND(Start@row <= DATE(2021, 12, 31), Finish@row >= DATE(2021, 12, 01)), IF(AND(MONTH(Start@row) = MONTH(Finish@row), YEAR(Start@row) = YEAR(Finish@row)), [Estimated hours]@row)

Bobbie P

That sounds perfect! Yes, I would love to implement the best solution for this and welcome your continued assistance Paul. Will await your next direction :)

Paul Newcome

Alright. Now for the tricky part. Spanning multiple months. We actually need to break this down into three sections, roll them up, then combine it with the above.

Part 1: Starting Month

=IF(AND(MONTH(Start@row) = 12, YEAR(Start@row) = 2021), NETWORKDAYS(Start@row, IFERROR(DATE(YEAR(Start@row), MONTH(Start@row) + 1, 1), DATE(YEAR(Start@row) + 1, 1, 1)) - 1) * 8)

Lets test this by putting in a December 2021 Start and a January 2022 Finish. Make sure the Estimated Hours that we want to split add up to something that makes sense for the dates you plug in.

Please note the bold numbers. These will need to be updated for the other columns. I just want to make sure we have this working first before moving on.