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

Find more posts tagged with

Human Resources

Formulas

Comments

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