SUMIFS totaling time worked for date range

jb@59069

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.

Paul Newcome

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

Paul Newcome

Excellent. Glad you got it figured out.