Formula to Calculate all screening dates in last 7 days and last 30 days.

Darcie Wilson

I am seeking a formula that will calculate all dates that occurred within the last 7 and 30 days...based on the "Screening Date" column...and should not include weekends if possible.

Thank you 

Shaine Greenwood

Hi Darcie,

Not exactly sure how you're wanting this to look in your sheet, but you might consider using the dependencies functionality for this and creating 7 day tasks, or creating tasks with 7 day lag time. Dependencies treats Sat-Sun as non-working days by default. More information on dependencies is available in the help article: https://help.smartsheet.com/articles/765727-enabling-dependencies-using-predecessors

Otherwise, you'd need to add one formula per each date cell you have that contains either the 7 day or 30 day mark with the WORKDAY function like in the examples below:

=WORKDAY([Screening Date]1, 7)

=WORKDAY([Screening Date]1, 30)

More on WORKDAY here: https://help.smartsheet.com/function/workday