# 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

• Employee
Answer ✓
Options

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

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭
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)

• Employee
Answer ✓
Options

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

• ✭✭✭✭
Options

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

• Employee
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!