Date Formula
I have a list of people who are working on multiple projects throughout the year. Each project/employee has a different start and end date. My goal is to flag a person who is currently on 3+ projects at once. I used a formula to count the projects each person is on, but I would like to take dates into consideration. For example, how many projects is Employee A on between today and the end of the year. The issue is I need it to automatically update each day to be the current day.
Formulas I used:
=COUNTIF({Project Resources Range 1}, "Employee A") - Which gave me a total project count
=IF([Active Projects]@row>=3,1) - Which flagged rows that had 3+ projects
I need a formula that says in addition to the formulas above, only look for projects from 10/5/22 (I want this date to automatically change daily) -12/31/22.
Is this possible in Smartsheet?
Answers
-
Try this in a flag column:
=IF(AND([project date]@row - TODAY() > 0, [project date]@row - TODAY() <= DATE(2022, 12, 31) - TODAY()), 1, 0)
-
Then use those flags as part of your other formulas if that makes sense.
-
Basically it flags if the date is between today and 12-31-22 and changes daily because of the TODAY() portion.
-
I think something similar would work. How would I adjust the formula to search an entire column and not just a row (@row)? Each person is on many different projects listed in my sheet (each employee is listed many times, not just one row). I need three things to be considered when something is flagged (date (solved above), #of projects within the date, and a persons name)? I'm trying to show that Employee A is on x number of open projects during a date range.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!