Referenced Sheet Hours Calculation Incorrect

Options

I have a time report that works on the time method of 1 = 1 hour, 0.25 =15 minutes, 0.50 = half an hour, 0.75 = 45 minutes, etc. If I do a SUM calculation within the sheet, I get the correct amount of time i.e. 37.50 for one week. However, on a metrics sheet, I am trying to calculate from this referenced time report, the monthly and weekly time, and I get a completely different amount i.e. 29.50. Why would it be doing this and how do I fix it?

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    Hi @Michelle Maas

    It looks like you may be missing the = sign before your Start Date, which means it's only looking at greater than Jan 1st, or Jan 4th. Should that date be included in the range?

    If so, try adding the =:

    =SUMIFS({Hours Column}, {Date Column}, AND(@cell >= DATE(2021, 1, 1), @cell <= DATE(2021, 1, 31), ISDATE(@cell)))

    =SUMIFS({Hours Column}, {Date Column}, AND(@cell >= DATE(2021, 1, 4), @cell <= DATE(2021, 1, 8), ISDATE(@cell)))

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Hi @Michelle Maas

    I hope you're well and safe!

    Strange!

    Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Michelle Maas
    Michelle Maas ✭✭✭✭
    Options

    For the month formula:

    =SUMIFS({Hours Column}, {Date Column}, AND(@cell > DATE(2021, 1, 1), @cell <= DATE(2021, 1, 31), ISDATE(@cell)))

    For the week formula:

    =SUMIFS({Hours Column}, {Date Column}, AND(@cell > DATE(2021, 1, 4), @cell <= DATE(2021, 1, 8), ISDATE(@cell)))

    The Hours Column has a SUM formula for the row, capturing the types of hours columns:

    =SUM([Type of Hours Start]@row:[Type of Hours End]@row)

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    Hi @Michelle Maas

    It looks like you may be missing the = sign before your Start Date, which means it's only looking at greater than Jan 1st, or Jan 4th. Should that date be included in the range?

    If so, try adding the =:

    =SUMIFS({Hours Column}, {Date Column}, AND(@cell >= DATE(2021, 1, 1), @cell <= DATE(2021, 1, 31), ISDATE(@cell)))

    =SUMIFS({Hours Column}, {Date Column}, AND(@cell >= DATE(2021, 1, 4), @cell <= DATE(2021, 1, 8), ISDATE(@cell)))

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Michelle Maas
    Michelle Maas ✭✭✭✭
    Options

    @Genevieve P. Wow, such a simple fix! Thank you, it works great now.

  • Genevieve P.
    Options

    No problem at all! 🙂

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!