If a date is within 7 days of todays date

Action Tracker

Hi Craig,

I was wondering if you could help me on getting something that I think is simple But couldn't make it yet.

I want an IF formula as follow:

=IF Due Date > today , value will be 1 , otherwise 0.

This is to be able and calculates a % of Adherence.

 

-Carlos

 

 

 

 

 

 

J. Craig Williams

Assuming your Due Date is in a column named [Due Date] and in the same row:

=IF([Due Date]@row > TODAY(), 1, 0)

If you want to put this in a Checkbox type column (which has values of 1 and 0 for checked/unchecked)

=[Due Date]@row > TODAY()

also works.

Craig

LouiseM

Hi,

Craig, I wonder if you could help me too?

I have a shared task list in smartsheet and i'm trying to work out how i can highlight a row if the due date for the task goes over by 1 day, automatically.

So for example, if a task had a due date of 05/06/2018 if it then was not completed by today, tomorrow when i check the task list, that task will be highlighted 'red' for example.

Thank you in advance! I have been searching everywhere to find out how to do this, and to no avail so i'm hoping someone can help me!

Ps. the due date column in the task list is set up as a 'date' as opposed to a 'text/number'- not sure if this makes a difference.

J. Craig Williams

LouiseM,

You will need to create a new column. I call mine "Overdue". Make it a Checkbox type column.

Assuming your due date column is named [Due Date] and your status of completion is in a column named [Done], then

=AND(NOT(Done@row), [Due Date]@row < TODAY(), ISDATE([Due Date]@row))

If you prefer IF statements, then this works too

=IF(AND(NOT(Done@row), [Due Date]@row < TODAY(), ISDATE([Due Date]@row)),1,0)

If you leave out the ISDATE() part, the check box is checked for blank dates.

In Conditional Formatting, set your format when the check box is checked.

Craig

 

LouiseM

Hi Craig,

Wow. You really are our Smartsheet Hero.

Thank you so much for your help. A super fast response and it worked perfectly first time.

I honestly have been looking for a way to do this on and off for about 2 months, so thank you.

J. Craig Williams

Formulas are easy. The hard part is getting people to be clear with what they want the formula to do.

Glad I could help.

Craig

jthirasawat85031

Hi Craig,

How would I go about this same type of formula, but have it recognize and factor in weekends?



Right now I have an "In Hands Date" Column, and I'm trying to figure out how to write a formula in a SLA column that would recognize that "In Hands Date" +2 Business days, which is when we should be closing the order out. How do I get the formula to recognize this, while also factoring in the weekend when we're not at work? 



I would need anything past the 2 Business days to flag with conditional formatting, so I'm looking for a return number and not a date. 



Example. 

If my In Hands date is 4/10/19. I would need my SLA column to flag as of 4/15/19 (4/13 - 4/14 are weekends).