Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

SUMIF or SUMIFS for time accrued after a date

Options
jb@59069
jb@59069 ✭✭✭✭✭✭
edited 12/09/19 in Archived 2017 Posts

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

 

Comments

  • Schiff A.
    Schiff A. Employee
    Options

    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

This discussion has been closed.