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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@Genevieve P. Wow, such a simple fix! Thank you, it works great now.
-
No problem at all! 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!