Referenced Sheet Hours Calculation Incorrect
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
-
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
-
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.
-
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)
-
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 -
@Genevieve P. Wow, such a simple fix! Thank you, it works great now.
-
No problem at all! 🙂
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!