SUMIFS totaling time worked for date range
Hello all.
We have a new client that has agreed to a monthly retainer contract, with invoicing on (or about) the 15th and end of each month. I need to create a report to track time and expenses for each time period for our own tracking and scheduling as well as reporting to our client, so we know when to cease work for a given month or alert the client that we are getting close to reaching their monthly retainer limit.
We use Smartsheet forms for tracking time and expenses with "Date Worked" and "Date Charged" columns. This process for tracking time and expenses has been working VERY well since implemented and saves a LOT of time and effort. Our employees and contractors are VERY happy with this process.
So what I need help with is a way to sum hours worked for say, 9/16/2018 - 9/30/2018, 10/1/2018 - 10/15/2018, etc., and same for any accrued expenses for the same period.
I have formulae to sum each employee/contractor en total, but am struggling with how to incorporate the date ranges.
I do believe that once this formula issue is resolved, I can create alerts/notifications or conditional formatting to alert us when we have reached certain percentages of budget.
Thank you for any help.
Comments
-
To input a specific date into a formula you would use the DATE function. DATE(myyyy,mm,dd). Something along the lines of
=SUMIFS(Hours:Hours, [Date Column Name]:[Date Column Name], > DATE(2018,09,30), [Date Column Name]:[Date Column Name], <= DATE(2018,10,15))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Excellent. Glad you got it figured out.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
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!