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
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 200 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 445 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives