Date Formula

jackiestone05

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?

Michael Culley

Try this in a flag column:

=IF(AND([project date]@row - TODAY() > 0, [project date]@row - TODAY() <= DATE(2022, 12, 31) - TODAY()), 1, 0)

Michael Culley

Then use those flags as part of your other formulas if that makes sense.

Michael Culley

Basically it flags if the date is between today and 12-31-22 and changes daily because of the TODAY() portion.

jackiestone05

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.