Formula for Hours Scheduled by Month

SarahALLBRiGHT

We're trying to determine how many hours we have scheduled in a month for a particular project based on the start and end date of the project. I can't quite figure out how to set the MAX/MIN without the EOM function. Can someone please help?

Genevieve P.

Hi @SarahALLBRiGHT

The way I would do this is first find the number of working days per month, then multiply that by the "Hours per Day".

We can use the MONTH function to find out if the Start Date is in the month you're looking for, and if it is, then use NETWORKDAYS Function to find the Start Date to End of Month number of working days.

=IF(MONTH([Start Date]@row) = 3, NETWORKDAYS([Start Date]@row, DATE(2022, 03, 31))

Notice how I use the DATE Function to identify the end of the month.

Then we can add the hours on to this by taking that number and multiplying (using *) by the Hours per Day:

=IF(MONTH([Start Date]@row) = 3, NETWORKDAYS([Start Date]@row, DATE(2022, 03, 31)) + " days & " + (NETWORKDAYS([Start Date]@row, DATE(2022, 03, 31)) * [Hours per Day]@row) + " hours"

But if the Start Date isn't in that month, we'll need to check if the End Date is in that month. If it is, we can find the number of working days from the Start of March to the End Date specified:

IF(MONTH([End Date]@row) = 3, NETWORKDAYS(DATE(2022, 03, 01), [End Date]@row)

and again, add on the Hours:

IF(MONTH([End Date]@row) = 3, NETWORKDAYS(DATE(2022, 03, 01), [End Date]@row) + " days & " + (NETWORKDAYS(DATE(2022, 03, 01), [End Date]@row) * [Hours per Day]@row) + " hours"

Otherwise, if neither of the dates are in the Month, we'll need to see if the Start is earlier than our month, and the end is later than our month, meaning that the entire month is in our range:

IF(AND([Start Date]@row < DATE(2022, 03, 01), [End Date]@row > DATE(2022, 03, 31)), NETWORKDAYS(DATE(2022, 03, 01), DATE(2022, 03, 31)) + " days & " + (NETWORKDAYS(DATE(2022, 03, 01), DATE(2022, 03, 31)) * [Hours per Day]@row) + " hours"

Full Formula with all 3 possibilities:

=IF(MONTH([Start Date]@row) = 3, NETWORKDAYS([Start Date]@row, DATE(2022, 03, 31)) + " days & " + (NETWORKDAYS([Start Date]@row, DATE(2022, 03, 31)) * [Hours per Day]@row) + " hours", IF(MONTH([End Date]@row) = 3, NETWORKDAYS(DATE(2022, 03, 01), [End Date]@row) + " days & " + (NETWORKDAYS(DATE(2022, 03, 01), [End Date]@row) * [Hours per Day]@row) + " hours", IF(AND([Start Date]@row < DATE(2022, 03, 01), [End Date]@row > DATE(2022, 03, 31)), NETWORKDAYS(DATE(2022, 03, 01), DATE(2022, 03, 31)) + " days & " + (NETWORKDAYS(DATE(2022, 03, 01), DATE(2022, 03, 31)) * [Hours per Day]@row) + " hours")))

Let me know if this works for you!

Cheers,

Genevieve

Paul Newcome

In addition to @Genevieve P.'s solution...

Since SS does not have an EOM function, we basically find the first of the next month and subtract 1 day from it.

=IFERROR(DATE(YEAR([Start Date]@row), MONTH([Start Date]@row) + 1, 1), DATE(YEAR([Start Date]@row) + 1, 1, 1)) - 1

Genevieve P.

Haha, @Paul Newcome saving the day with a much more concise formula. Thank you!

Paul Newcome

@Genevieve P. Oh. that is most definitely NOT a replacement for the overall formula. that's just how to do strictly the EOM function in Smartsheet. Haha. It would actually make your formula longer because it automates the last day of the month portion.

=IF(MONTH([Start Date]@row) = 3, NETWORKDAYS([Start Date]@row, IFERROR(DATE(YEAR([Start Date]@row), MONTH([Start Date]@row) + 1, 1), DATE(YEAR([Start Date]@row) + 1, 1, 1)) - 1) + " days & " + (NETWORKDAYS([Start Date]@row, IFERROR(DATE(YEAR([Start Date]@row), MONTH([Start Date]@row) + 1, 1), DATE(YEAR([Start Date]@row) + 1, 1, 1)) - 1) * [Hours per Day]@row) + " hours", IF(MONTH([End Date]@row) = 3, NETWORKDAYS(DATE(2022, 03, 01), [End Date]@row) + " days & " + (NETWORKDAYS(DATE(2022, 03, 01), [End Date]@row) * [Hours per Day]@row) + " hours", IF(AND([Start Date]@row < DATE(2022, 03, 01), [End Date]@row > IFERROR(DATE(YEAR([Start Date]@row), MONTH([Start Date]@row) + 1, 1), DATE(YEAR([Start Date]@row) + 1, 1, 1)) - 1), NETWORKDAYS(DATE(2022, 03, 01), IFERROR(DATE(YEAR([Start Date]@row), MONTH([Start Date]@row) + 1, 1), DATE(YEAR([Start Date]@row) + 1, 1, 1)) - 1) + " days & " + (NETWORKDAYS(DATE(2022, 03, 01), IFERROR(DATE(YEAR([Start Date]@row), MONTH([Start Date]@row) + 1, 1), DATE(YEAR([Start Date]@row) + 1, 1, 1)) - 1) * [Hours per Day]@row) + " hours")))

Genevieve P.

https://media.giphy.com/media/udmx3pgdiD7tm/giphy.gif

SarahALLBRiGHT

Wow y'all are wizards! It worked!

However, I was just looking for the formula to return the hours only so that we can total the number of hours for the month for all our jobs. Can you please provide a revised formula?

My apologies. My example was not clear. I wrote out the number of days and hours to show how the formula needed to split the number of working days in the month to get the hours scheduled that month.

Genevieve P.

Hi @SarahALLBRiGHT

Do you have a set number of hours per day? Ex. 8 hours per day? If you do, we could multiply the NETWORKDAYS by your set hours to find how many hours were worked that month.

In our examples we're multiplying by [Hours per Day]@row but you could do 8 directly in the formula instead.

We can also take out the "# days" bit to make it shorter.

=IF(MONTH([Start Date]@row) = 3, (NETWORKDAYS([Start Date]@row, IFERROR(DATE(YEAR([Start Date]@row), MONTH([Start Date]@row) + 1, 1), DATE(YEAR([Start Date]@row) + 1, 1, 1)) - 1) * 8) + " hours", IF(MONTH([End Date]@row) = 3, (NETWORKDAYS(DATE(2022, 03, 01), [End Date]@row) * 8) + " hours", IF(AND([Start Date]@row < DATE(2022, 03, 01), [End Date]@row > IFERROR(DATE(YEAR([Start Date]@row), MONTH([Start Date]@row) + 1, 1), DATE(YEAR([Start Date]@row) + 1, 1, 1)) - 1), (NETWORKDAYS(DATE(2022, 03, 01), IFERROR(DATE(YEAR([Start Date]@row), MONTH([Start Date]@row) + 1, 1), DATE(YEAR([Start Date]@row) + 1, 1, 1)) - 1) * 8) + " hours")))

Is this what you were looking for?

SarahALLBRiGHT

@Genevieve P. Not exactly, see we may have multiple people working on the project each day. So the hours produced is the crew size times 8 hours in a day. Below is my sheet layout with actual column names. If you could help me figure out the hours produced/booked per month I would appreciate it.

I attempted to adapt your previous formula but as you can see it returns more hours booked than the total man hours. Not sure how to correct.

=IF(MONTH([Ops Start Date]@row) = 1, (NETWORKDAYS([Ops Start Date]@row, DATE(2022, 1, 31)) * [Hours Produced Per Day]@row), IF(MONTH([Complete Date]@row) = 1, (NETWORKDAYS(DATE(2022, 1, 1), [Complete Date]@row) * [Hours Produced Per Day]@row), IF(AND([Ops Start Date]@row < DATE(2022, 1, 1), [Complete Date]@row > DATE(2022, 1, 31)), (NETWORKDAYS(DATE(2022, 1, 1), DATE(2022, 1, 31)) * [Hours Produced Per Day]@row))))

Or if we should approach this another way. Perhaps by using the hours produced in the month divided by the total man hours of the project to get a percentage of hours that will be produced in that month then times that by the total man hours?

Paul Newcome

Do you have additional columns for February, March, April, so on and so forth?

How are you using these totals? Are they strictly informational, or are you pulling them into a metrics sheet for charts, etc.?

What is the absolute longest duration you could possibly need to account for?

SarahALLBRiGHT

Hi @Paul Newcome! Yes I have one column for each month. From there will be pulling monthly totals into a metric sheet on a production dashboard so we can see how booked we are for the month. Longest duration would be 4-5 months, I'd say. Thanks for jumping in. Feel like we're close!

Paul Newcome

Hmm... Ok. I feel like we should be pretty close to a solution.

We know we need NETWORKDAYS. The start date for the function is going to be either the first day of the month or the ops start date depending on when the row's start date is (and end date). I will stick with the January formula as there should only be some basic tweaks needed for the other months.

=NETWORKDAYS(IF(MONTH([Ops Start Date]@row) = 1, [Ops Start Date]@row, IF(AND(MONTH([Ops Start Date]@row) < 1, MONTH(Complete Date]@row) >= 1), DATE(2022, 01, 01))),

The end date for the function is going to be either the complete date or the last day of the month.

=NETWORKDAYS(IF(MONTH([Ops Start Date]@row) = 1, [Ops Start Date]@row, IF(AND(MONTH([Ops Start Date]@row) < 1, MONTH(Complete Date]@row) >= 1), DATE(2022, 01, 01))), IF(MONTH([Complete Date]@row) = 1, [Complete Date]@row, IF(AND(MONTH([Ops Start Date]@row) < 1, MONTH(Complete Date]@row) >= 1), DATE(2022, 01, 31))))

Then we use an IFERROR to output zero if the dates aren't populated correctly (because there was no time within the month of January) and multiply by the hours produced per day.

=IFERROR(NETWORKDAYS(IF(MONTH([Ops Start Date]@row) = 1, [Ops Start Date]@row, IF(AND(MONTH([Ops Start Date]@row) < 1, MONTH(Complete Date]@row) >= 1), DATE(2022, 01, 01))), IF(MONTH([Complete Date]@row) = 1, [Complete Date]@row, IF(AND(MONTH([Ops Start Date]@row) < 1, MONTH(Complete Date]@row) >= 1), DATE(2022, 01, 31)))), 0) * [Hours Produced Per Day]@row

How is this one working? To make the adjustments for each of the different months, you should be able to update the numbers we are comparing the months to as well as the month portion of each DATE function.

I haven't had a chance to test this one yet though. If I get a chance before you do, I will reply with either it is working for me or a different formula.

Paul Newcome

Looks like I missed a couple of square brackets and I am adding an = (testing formula was for current month).

=IFERROR(NETWORKDAYS(IF(MONTH([Ops Start Date]@row) = 4, [Ops Start Date]@row, IF(AND(MONTH([Ops Start Date]@row) < 4, MONTH([Complete Date]@row) >= 4), DATE(2022, 4, 1))), IF(MONTH([Complete Date]@row) = 4, [Complete Date]@row, IF(AND(MONTH([Ops Start Date]@row) <= 4, MONTH([Complete Date]@row) >= 4), DATE(2022, 4, 30)))), 0) * [Hours Produced Per Day]@row

Also keep in mind... This will not work if either the start date or end date is in a different year. If you are going to need to accommodate that, then my suggestion would be a bit of a restructure. Let me know if that's the case, and I will be happy to help walk you through that if interested.

Kayla

@Paul Newcome - I'm trying to do the same thing as this thread, but in a cross sheet so that I can span it over multiple years and I don't need a column per month in the source sheet.

The 'Schedule' Sheet has the data, and I want to calculate the 'Total Work Hours' in this metrics sheet.

Any ideas or help would be greatly (more than that) appreciated!!

Paul Newcome

@Kayla You would need to create additional columns on the source sheet and use formulas there to output how many hours were worked in each month on each row. Then your metrics sheet would use a basic SUM function (or SUMIFS depending on your structure) to grab from each of the helper columns.

The solution for spreading the number of days out across each month can be found above.

SarahALLBRiGHT

https://community.smartsheet.com/discussion/comment/326148#Comment_326148

@Paul Newcome Hi Paul! Could you please help with a formula which will calculate hours per month when start/end dates are in different years? Thanks!