Date range formula help
@Paul Newcome looking for your expertise again!
I'm looking to automate/expatiate our time keeping process to pay subs. I've had no luck after a few hours, so figured I'd reach out. See the screen shots below. The goal is to have a each employee and their total weeks pay consolidated on a line based on a specific date range. I'm totally open to adding/removing any columns. I'm thinking it's going to be along the lines of this, but I'm striking out =SUMIFS({HDZ Red plant labor Range 1}, {HDZ Red plant labor Range 3}, @cell = [Employee Name]1, @cell >= [Pay period]1, @cell <= [Pay Period End]1)
Best Answers
-
Try working on something along the lines of...
=SUMIFS({Other Sheet Column to Sum}, {Other Sheet Employee Column}, [Employee Name]@row, {Other Sheet Date Column}, AND(@cell >= [Pay Period]@row, @cell <= [Pay Period End]@row))
-
Yes and no. You can set up a sheet that contains their names and rates, but you would still need to at least manually enter their names on the tracking sheet so that formulas would have something to compare against.
Answers
-
Try working on something along the lines of...
=SUMIFS({Other Sheet Column to Sum}, {Other Sheet Employee Column}, [Employee Name]@row, {Other Sheet Date Column}, AND(@cell >= [Pay Period]@row, @cell <= [Pay Period End]@row))
-
Perfect!! Thanks!!
-
Happy to help! 👍️
-
@Paul Newcome Hey Paul, want to pick your brain again on this sheet. I'm running into our subcontractor bringing in new employees fairly regularly (rotating them based on needs really). Is there a way, if I have something along a "master employee list", that I can add the new names & their rates, and have my main time sheet (above) auto-populated? I'm currently going in and manually adding (copy and paste to each day) a line to each day with the new employee.
-
Yes and no. You can set up a sheet that contains their names and rates, but you would still need to at least manually enter their names on the tracking sheet so that formulas would have something to compare against.
-
That's what I assumed, thanks!
-
Happy to help! 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!