Flagging task if not updated

Lindseyh111241

Hi, 

I have a sheet that tracks a group of teams' weekly KPI's. Each week on a Thursday, the different team members need to access their sheet, update the date that their KPI's relate to and enter the KPI's for each line. Each team has a separate sheet that rolls up into a "AT RISK" task tracking report for me. 

I have to collate these KPI's and add it to a Excom report. I also have other on-going activities in each sheet that have normal due dates. 

I need to have the KPI tasks flagged and therefore appear in my AT RISK report if the date has not been updated for the current week, but I do not want this to be flagged for the whole week after the previous Friday leading up to the due date.

The team's due date in every Thursday, so I would like them to be flagged on the Thursday and Friday, if the date is not updated to the current week's Thursday or Friday, but then to remain unflagged for Monday, Tuesday and Wednesday of the next week.  

I am struggling to get a formula to get this to work. 

Can anyone please help me with this? 

Eric M Oliveira

Hi Lindsey,

 

Happy to help! If your desired goal is to only flag a flag column if it is Thursday or Friday, only if the updated date column hasn't been updated to the current week, you can achieve this utilizing a formula similar to the below.

 

=IF(AND(OR(WEEKDAY([Date Column]1) = 5, WEEKDAY([Date Column]1) = 6), NOT(WEEKNUMBER(TODAY()) = WEEKNUMBER([Date Column]1))), 1, 0)

 

WEEKDAY reference the day of the week 1-7, 5 & 6 being Thursday and Friday.

WEEKNUMBER reference the week number in the span of the year, being 52.

 

There is no need to reply to this email if the issue is resolved. As a valued customer your feedback is important. Please let me know how I did by clicking on the stars at the bottom of this email.

 

Have a wonderful day. Thank you for contacting Smartsheet Support.

Cheers,

Eric

Smartsheet Technical Support