SUMIF or SUMIFS for time accrued after a date

jb@59069

Hello,

I have time tracking sheets with Date Worked and Time worked columns.

I need to create a formula for totaling Time Worked after a specific date.

Say that 11/1/2017 is the cutoff and I need a sum of all time or time by a specific employee after that date.

Is there a way to do this?

Thanks.

Jerry

 

Schiff A.

Hi Jerry,

For tracking hours worked in this way, you can take advantage of the @cell argument. In this context, we can leverage @cell to  check if each date in the Date Worked column is after 11/01/2017



Here are 2 similar formulas. Each will accomplish the same goal:



1. Here is a version that references a date in row 1 of a column called "Cutoff Date".  This version makes it easy to change the date if you have many instances of the formula that all use the same date.

=SUMIF(Date:Date, @cell > [Cutoff Date]1, Time:Time)

 

2. Here is a version that specifies the date within the formula. This is best suited for situations where you don't expect to change the date in each instance of the formula.

=SUMIF([Date Worked]:[Date Worked], @cell > DATE(2017, 11, 1), [Time Worked]:[Time Worked])

 

More information about @cell here: https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell

 

Thanks!

Schiff