Formula for Hours Scheduled by Month

Options
2»

Answers

  • Tore
    Tore ✭✭
    Options

    Hi,

    I'm looking for something very similar.

    image.png

    I would like to get total work hours per month. Anyone have a solution for a formula that I can put in each month-column to get the estimated hours for that specific month?

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee Admin
    edited 11/01/23
    Options

    Hey @Tore

    Try something like this for June, 2023:

    =IF(AND([Start Date]@row = [End Date]@row, MONTH([Start Date]@row) = 6), [Hours Per Day]@row, IF(AND([Start Date]@row <= DATE(2023, 6, 1), [End Date]@row >= DATE(2023, 6, 30)), [Hours Per Day]@row * 22, IF(OR(MONTH([End Date]@row) < 6, MONTH([Start Date]@row) > 6), 0, IF(AND(MONTH([Start Date]@row) = 6, MONTH([End Date]@row) = 6), NETWORKDAYS([Start Date]@row, [End Date]@row) * [Hours Per Day]@row, IF([Start Date]@row >= DATE(2023, 6, 1), NETWORKDAYS([Start Date]@row, DATE(2023, 6, 30)) * [Hours Per Day]@row, NETWORKDAYS(DATE(2023, 6, 1), [End Date]@row) * [Hours Per Day]@row)))))


    I'll break down each section & statement:

    • If start and end are the same, meaning 1 day, and that day in the Month you're looking for, then return the Hours Per Day value exactly as-is:

    =IF(AND([Start Date]@row = [End Date]@row, MONTH([Start Date]@row) = 6)

    • If the start date is before this month and the end date is after this month, multiply the hours by day by 22 (working days for June)

    IF(AND([Start Date]@row <= DATE(2023, 6, 1), [End Date]@row >= DATE(2023, 6, 30)), [Hours Per Day]@row * 22,

    • If the Start is either in the future, or the End is in the past, return 0 for this month (no working hours)

    IF(OR(MONTH([End Date]@row) < 6, MONTH([Start Date]@row) > 6), 0,

    • If the start and end dates are in the same month (and it's the month we want), return the NETWORKDAYS between the two dates and multiply it by the Hours Per Day

    IF(AND(MONTH([Start Date]@row) = 6, MONTH([End Date]@row) = 6), NETWORKDAYS([Start Date]@row, [End Date]@row) * [Hours Per Day]@row, 

    • If the start is greater than the first of this current month, get the Network days between the start date and the end of the month and multiply it by the Hours Per Day

    IF([Start Date]@row >= DATE(2023, 6, 1), NETWORKDAYS([Start Date]@row, DATE(2023, 6, 30)) * [Hours Per Day]@row, 

    • Otherwise, get the Network Days between the start of the month and the end date of this current row and multiply it by the Hours Per Day

    NETWORKDAYS(DATE(2023, 6, 1), [End Date]@row) * [Hours Per Day]@row))))) 



    Keep in mind that you'll need to change out the variables for each month, such as the Month number, the DATE values, and the working days. So for July 2023, this is how we'd change it:

    =IF(AND([Start Date]@row = [End Date]@row, MONTH([Start Date]@row) = 7), [Hours Per Day]@row, IF(AND([Start Date]@row <= DATE(2023, 7, 1), [End Date]@row >= DATE(2023, 7, 31)), [Hours Per Day]@row * 21, IF(OR(MONTH([End Date]@row) < 7, MONTH([Start Date]@row) > 7), 0, IF(AND(MONTH([Start Date]@row) = 7, MONTH([End Date]@row) = 7), NETWORKDAYS([Start Date]@row, [End Date]@row) * [Hours Per Day]@row, IF([Start Date]@row >= DATE(2023, 7, 1), NETWORKDAYS([Start Date]@row, DATE(2023, 7, 31)) * [Hours Per Day]@row, NETWORKDAYS(DATE(2023, 7, 1), [End Date]@row) * [Hours Per Day]@row)))))


    Screenshot 2023-11-01 at 14.11.36.png

    Let me know if this works for you or if I've misunderstood what you're looking to do!

    Cheers,

    Genevieve

    · Share on FacebookShare on Twitter
  • Tore
    Tore ✭✭
    edited 11/01/23
    Options

    Thank you so much @Genevieve P.

    This helps a lot!

    Ref. "If the start date is before this month and the end date is after this month, multiply the hours by day by 22 (working days for June)"... is there a way to automatically get working days for the specific month? Could NETWORKDAYS be used somehow to fix this without me having to find that out for each month?

    Do you also know if there is a way that I could add some help columns, or in another way link the June column to "6", and the July column to "7" so that the formula could reference these instead of me changing that manually in the formula for each month column?

    Also, there is a bug in the formula when I put it in for December. Are you able to tell what's wrong? (don't worry about the "21" working days, I'll update this later)

    =IF(AND([Start Date]@row = [End Date]@row, MONTH([Start Date]@row) = 12), [Hours per Day]@row, IF(AND([Start Date]@row <= DATE(2023, 12, 1), [End Date]@row >= DATE(2023, 12, 31)), [Hours per Day]@row * 21, IF(OR(MONTH([End Date]@row) < 12, MONTH([Start Date]@row) > 12), 0, IF(AND(MONTH([Start Date]@row) = 12, MONTH([End Date]@row) = 12), NETWORKDAYS([Start Date]@row, [End Date]@row) * [Hours per Day]@row, IF([Start Date]@row >= DATE(2023, 12, 1), NETWORKDAYS([Start Date]@row, DATE(2023, 12, 31)) * [Hours per Day]@row, NETWORKDAYS(DATE(2023, 12, 1), [End Date]@row) * [Hours per Day]@row)))))

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hey @Tore

    1 ) Yes! You could use NETWORDAYS between that month's Start and End dates, yes.

    =IF(...IF(AND([Start Date]@row <= DATE(2023, 7, 1), [End Date]@row >= DATE(2023, 7, 31)), [Hours Per Day]@row * NETWORKDAYS(DATE(2023, 7, 1),DATE(2023, 7, 31)), IF(...etc

    2) Yes, you could add the Month Number as the top row of your sheet, if you'd prefer. Then reference this with an Absolute Reference $ to ensure it stays linked as you update your formula.

    =IF(AND([Start Date]@row = [End Date]@row, MONTH([Start Date]@row) = [July 2023]$1), [Hours Per Day]@row, IF(AND([Start Date]@row <= DATE(2023, 7, 1), [End Date]@row >= DATE(2023, 7, 31)), [Hours Per Day]@row * NETWORKDAYS(DATE(2023, 7, 1), DATE(2023, 7, 31)), IF(OR(MONTH([End Date]@row) < [July 2023]$1, MONTH([Start Date]@row) > [July 2023]$1), 0, IF(AND(MONTH([Start Date]@row) = [July 2023]$1, MONTH([End Date]@row) = [July 2023]$1), NETWORKDAYS([Start Date]@row, [End Date]@row) * [Hours Per Day]@row, IF([Start Date]@row >= DATE(2023, 7, 1), NETWORKDAYS([Start Date]@row, DATE(2023, 7, 31)) * [Hours Per Day]@row, NETWORKDAYS(DATE(2023, 7, 1), [End Date]@row) * [Hours Per Day]@row)))))


    This does mean you won't be able to set it as a column formula, but you could then drag-fill the formula and as new rows are added they will update automatically.

    Screenshot 2023-11-02 at 14.29.13.png


    You'll still need to adjust any of the DATE() values since they lock in a Year, Month, and Day, which will vary for each of your columns.


    3) For December, we can't look to see if the MONTH is "greater than 12" or if the End Date is "Less than 12" for a future date because the next month is 1.

    You'll want to add in the exact date instead:

    IF(OR([End Date]@row < DATE(2023, 12, 1), MONTH([Start Date]@row) < 12), 0,

    =IF(AND([Start Date]@row = [End Date]@row, MONTH([Start Date]@row) = 12), [Hours Per Day]@row, IF(AND([Start Date]@row <= DATE(2023, 12, 1), [End Date]@row >= DATE(2023, 12, 31)), [Hours Per Day]@row * 21, IF(OR([End Date]@row < DATE(2023, 12, 1), MONTH([Start Date]@row) < 12), 0, IF(AND(MONTH([Start Date]@row) = 12, MONTH([End Date]@row) = 12), NETWORKDAYS([Start Date]@row, [End Date]@row) * [Hours Per Day]@row, IF([Start Date]@row >= DATE(2023, 12, 1), NETWORKDAYS([Start Date]@row, DATE(2023, 12, 31)) * [Hours Per Day]@row, NETWORKDAYS(DATE(2023, 12, 1), [End Date]@row) * [Hours Per Day]@row)))))


    Let me know if this all makes sense and now works for you!

    · Share on FacebookShare on Twitter
  • Tore
    Tore ✭✭
    Options

    Hi @Genevieve P.

    Getting closer to the solution every time.

    image.png

    Since I still need to manually adjust the dates for each column, I decided to skip the number top row as well.

    As you can see, I still have some errors that I don't understand. It seems to be a problem if the start date is before said month, but the end date is in said month. Ref. line 5 in my picture above. Even though my end date is Feb. 6th, there are no hours listed for the month of February on this line. and the same goes for lines 2 & 4. I think there is still some issue with the December formula, and there might be some issues occurring when one task starts in 2023, but ends in 2024. Also, the is a small deviation between Hours and Sum, which does not make sense since Sum just adds up all the months. Any idea what could cause the mismatch?

    Here are all the month formulas that I use:

    June 2023:

    =IF(AND([Start Date]@row = [End Date]@row, MONTH([Start Date]@row) = 6), [Hours per Day]@row, IF(AND([Start Date]@row <= DATE(2023, 6, 1), [End Date]@row >= DATE(2023, 6, 30)), [Hours per Day]@row * NETWORKDAYS(DATE(2023, 6, 1), DATE(2023, 6, 30)), IF(OR(MONTH([End Date]@row) < 6, MONTH([Start Date]@row) > 6), 0, IF(AND(MONTH([Start Date]@row) = 6, MONTH([End Date]@row) = 6), NETWORKDAYS([Start Date]@row, [End Date]@row) * [Hours per Day]@row, IF([Start Date]@row >= DATE(2023, 6, 1), NETWORKDAYS([Start Date]@row, DATE(2023, 6, 30)) * [Hours per Day]@row, NETWORKDAYS(DATE(2023, 6, 1), [End Date]@row) * [Hours per Day]@row)))))

    July 2023:

    =IF(AND([Start Date]@row = [End Date]@row, MONTH([Start Date]@row) = 7), [Hours per Day]@row, IF(AND([Start Date]@row <= DATE(2023, 7, 1), [End Date]@row >= DATE(2023, 7, 31)), [Hours per Day]@row * NETWORKDAYS(DATE(2023, 7, 1), DATE(2023, 7, 31)), IF(OR(MONTH([End Date]@row) < 7, MONTH([Start Date]@row) > 7), 0, IF(AND(MONTH([Start Date]@row) = 7, MONTH([End Date]@row) = 7), NETWORKDAYS([Start Date]@row, [End Date]@row) * [Hours per Day]@row, IF([Start Date]@row >= DATE(2023, 7, 1), NETWORKDAYS([Start Date]@row, DATE(2023, 7, 31)) * [Hours per Day]@row, NETWORKDAYS(DATE(2023, 7, 1), [End Date]@row) * [Hours per Day]@row)))))

    August 2023:

    =IF(AND([Start Date]@row = [End Date]@row, MONTH([Start Date]@row) = 8), [Hours per Day]@row, IF(AND([Start Date]@row <= DATE(2023, 8, 1), [End Date]@row >= DATE(2023, 8, 31)), [Hours per Day]@row * NETWORKDAYS(DATE(2023, 8, 1), DATE(2023, 8, 31)), IF(OR(MONTH([End Date]@row) < 8, MONTH([Start Date]@row) > 8), 0, IF(AND(MONTH([Start Date]@row) = 8, MONTH([End Date]@row) = 8), NETWORKDAYS([Start Date]@row, [End Date]@row) * [Hours per Day]@row, IF([Start Date]@row >= DATE(2023, 8, 1), NETWORKDAYS([Start Date]@row, DATE(2023, 8, 31)) * [Hours per Day]@row, NETWORKDAYS(DATE(2023, 8, 1), [End Date]@row) * [Hours per Day]@row)))))

    September 2023:

    =IF(AND([Start Date]@row = [End Date]@row, MONTH([Start Date]@row) = 9), [Hours per Day]@row, IF(AND([Start Date]@row <= DATE(2023, 9, 1), [End Date]@row >= DATE(2023, 9, 30)), [Hours per Day]@row * NETWORKDAYS(DATE(2023, 9, 1), DATE(2023, 9, 30)), IF(OR(MONTH([End Date]@row) < 9, MONTH([Start Date]@row) > 9), 0, IF(AND(MONTH([Start Date]@row) = 9, MONTH([End Date]@row) = 9), NETWORKDAYS([Start Date]@row, [End Date]@row) * [Hours per Day]@row, IF([Start Date]@row >= DATE(2023, 9, 1), NETWORKDAYS([Start Date]@row, DATE(2023, 9, 30)) * [Hours per Day]@row, NETWORKDAYS(DATE(2023, 9, 1), [End Date]@row) * [Hours per Day]@row)))))

    October 2023:

    =IF(AND([Start Date]@row = [End Date]@row, MONTH([Start Date]@row) = 10), [Hours per Day]@row, IF(AND([Start Date]@row <= DATE(2023, 10, 1), [End Date]@row >= DATE(2023, 10, 31)), [Hours per Day]@row * NETWORKDAYS(DATE(2023, 10, 1), DATE(2023, 10, 31)), IF(OR(MONTH([End Date]@row) < 10, MONTH([Start Date]@row) > 10), 0, IF(AND(MONTH([Start Date]@row) = 10, MONTH([End Date]@row) = 10), NETWORKDAYS([Start Date]@row, [End Date]@row) * [Hours per Day]@row, IF([Start Date]@row >= DATE(2023, 10, 1), NETWORKDAYS([Start Date]@row, DATE(2023, 10, 31)) * [Hours per Day]@row, NETWORKDAYS(DATE(2023, 10, 1), [End Date]@row) * [Hours per Day]@row)))))

    November 2023:

    =IF(AND([Start Date]@row = [End Date]@row, MONTH([Start Date]@row) = 11), [Hours per Day]@row, IF(AND([Start Date]@row <= DATE(2023, 11, 1), [End Date]@row >= DATE(2023, 11, 30)), [Hours per Day]@row * NETWORKDAYS(DATE(2023, 11, 1), DATE(2023, 11, 30)), IF(OR(MONTH([End Date]@row) < 11, MONTH([Start Date]@row) > 11), 0, IF(AND(MONTH([Start Date]@row) = 11, MONTH([End Date]@row) = 11), NETWORKDAYS([Start Date]@row, [End Date]@row) * [Hours per Day]@row, IF([Start Date]@row >= DATE(2023, 11, 1), NETWORKDAYS([Start Date]@row, DATE(2023, 11, 30)) * [Hours per Day]@row, NETWORKDAYS(DATE(2023, 11, 1), [End Date]@row) * [Hours per Day]@row)))))

    December 2023:

    =IF(AND([Start Date]@row = [End Date]@row, MONTH([Start Date]@row) = 12), [Hours per Day]@row, IF(AND([Start Date]@row <= DATE(2023, 12, 1), [End Date]@row >= DATE(2023, 12, 31)), [Hours per Day]@row * NETWORKDAYS(DATE(2023, 12, 1), DATE(2023, 12, 31)), IF(OR([End Date]@row < DATE(2023, 12, 1), MONTH([Start Date]@row) < 12), 0, IF(AND(MONTH([Start Date]@row) = 12, MONTH([End Date]@row) = 12), NETWORKDAYS([Start Date]@row, [End Date]@row) * [Hours per Day]@row, IF([Start Date]@row >= DATE(2023, 12, 1), NETWORKDAYS([Start Date]@row, DATE(2023, 12, 31)) * [Hours per Day]@row, NETWORKDAYS(DATE(2023, 12, 1), [End Date]@row) * [Hours per Day]@row)))))

    January 2024:

    =IF(AND([Start Date]@row = [End Date]@row, MONTH([Start Date]@row) = 1), [Hours per Day]@row, IF(AND([Start Date]@row <= DATE(2024, 1, 1), [End Date]@row >= DATE(2024, 1, 31)), [Hours per Day]@row * NETWORKDAYS(DATE(2024, 1, 1), DATE(2024, 1, 31)), IF(OR(MONTH([End Date]@row) < 1, MONTH([Start Date]@row) > 1), 0, IF(AND(MONTH([Start Date]@row) = 1, MONTH([End Date]@row) = 1), NETWORKDAYS([Start Date]@row, [End Date]@row) * [Hours per Day]@row, IF([Start Date]@row >= DATE(2024, 1, 1), NETWORKDAYS([Start Date]@row, DATE(2024, 1, 31)) * [Hours per Day]@row, NETWORKDAYS(DATE(2024, 1, 1), [End Date]@row) * [Hours per Day]@row)))))

    February 2024:

    =IF(AND([Start Date]@row = [End Date]@row, MONTH([Start Date]@row) = 2), [Hours per Day]@row, IF(AND([Start Date]@row <= DATE(2024, 2, 1), [End Date]@row >= DATE(2024, 2, 29)), [Hours per Day]@row * NETWORKDAYS(DATE(2024, 2, 1), DATE(2024, 2, 29)), IF(OR(MONTH([End Date]@row) < 2, MONTH([Start Date]@row) > 2), 0, IF(AND(MONTH([Start Date]@row) = 2, MONTH([End Date]@row) = 2), NETWORKDAYS([Start Date]@row, [End Date]@row) * [Hours per Day]@row, IF([Start Date]@row >= DATE(2024, 2, 1), NETWORKDAYS([Start Date]@row, DATE(2024, 2, 29)) * [Hours per Day]@row, NETWORKDAYS(DATE(2024, 2, 1), [End Date]@row) * [Hours per Day]@row)))))

    March 2024:

    =IF(AND([Start Date]@row = [End Date]@row, MONTH([Start Date]@row) = 3), [Hours per Day]@row, IF(AND([Start Date]@row <= DATE(2024, 3, 1), [End Date]@row >= DATE(2024, 3, 31)), [Hours per Day]@row * NETWORKDAYS(DATE(2024, 3, 1), DATE(2024, 3, 31)), IF(OR(MONTH([End Date]@row) < 3, MONTH([Start Date]@row) > 3), 0, IF(AND(MONTH([Start Date]@row) = 3, MONTH([End Date]@row) = 3), NETWORKDAYS([Start Date]@row, [End Date]@row) * [Hours per Day]@row, IF([Start Date]@row >= DATE(2024, 3, 1), NETWORKDAYS([Start Date]@row, DATE(2024, 3, 31)) * [Hours per Day]@row, NETWORKDAYS(DATE(2024, 3, 1), [End Date]@row) * [Hours per Day]@row)))))


    Again, thanks for all the help so far.

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Tore

    Ah, yes, you're correct - we didn't take into account tasks that span from one year to the next. As with the suggested adjustment for the December formula, you'll want to change all your formulas to look between the specific month's date range instead of the Month for the 0 statement.

    If we look at February as an example, this is why it's returning 0:

    IF(OR(MONTH([End Date]@row) < 2, MONTH([Start Date]@row) > 2), 0,

    See that it says "if the Month is greater than 2, return 0". In the row you're point out, the month is 12 (but the year before).

    At this point I would suggest adjusting all of the formulas to check for specific date ranges instead of Months:

    IF(OR([End Date]@row < DATE(2024,02,01), [Start Date]@row > DATE(2024,02,28)), 0,

    This allows you to look for both a Month and a Year. Does that make sense?


    This will also correct the issue with the December formula. Instead of:

    =IF(....IF(OR([End Date]@row < DATE(2023, 12, 1), MONTH([Start Date]@row) < 12), 0, ...

    Let's change it to:

    IF(OR([End Date]@row < DATE(2023, 12, 1), [Start Date]@row < DATE(2023, 12, 31)), 0,

    This will then update your row that has the 13th of December as the end date to populate the December column.


    For your SUM values - is it possible that there are further decimal points hidden in the cell, but the cells are rounding up to one or two points? The SUM value would be summing all the hidden decimals as well, meaning if something looks like 0.40 but is actually 0.39999 then the total sum could be a little lower than expected.

    Cheers,

    Genevieve

    · Share on FacebookShare on Twitter
  • Tore
    Tore ✭✭
    Options

    Hi @Genevieve P.

    Thanks! It works 😃

    The issue with SUM was how I had some holidays in the original schedule that changed the duration compared to this sheet without the holidays. Now, it's all fixed and works exactly as intended!

    Thank you!

    · Share on FacebookShare on Twitter
  • SarahALLBRiGHT
    SarahALLBRiGHT ✭✭
    Options
    https://community.smartsheet.com/discussion/comment/403001#Comment_403001

    Hey Tore, Would you mind posting your final formulas that worked? Thanks! :-)

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!