Flagging task if not updated

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?

Find more posts tagged with

Formulas

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Is there a way to create a 3-digit padding for numbers
Hi all, and thanks in advance! I wanted to know if there was any way to create "padding" in Smartsheet. Basically I have account numbers, and a handful are still 2-digit, so they appear as TDL-17, TDL-67, etc. Most of the accounts are 3-digit numbers like TDL-180. I want something that will recognize whether it is 2 or 3…
Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?