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))
-
This content has been removed.
-
Excellent. Glad you got it figured out.
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!