Sign in to join the conversation:
Hi
I am trying to create a formula to hight light a cell if the date it contains is within 7 days (prior) to todays date... Can anyone help, I have this working on a spreadsheet but cannot seem to get it working on smartsheet.
Thank you
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
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
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.
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.
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.
Formulas are easy. The hard part is getting people to be clear with what they want the formula to do.
Glad I could help.
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).
Hi all, I'm having some issues with an index-collect formula. I have (3) existing sheets that list a unique project name (text field), as well as a producer assigned to work that project. The producer column in all (3) sheets is set as a contact field. In a separate sheet, I'm trying to auto-populate the producer…
I am currently in business trial version, but i was told it is supposed to allow for full functionality, however when I click Ctr+F my search menu is only a search bar that allows me to only find the string, and it doesn't allow for replacing. Is it me or the F&R functionality rather limited?
I looking to find unique values, in order, and separate them into columns. The "Client Update Date" column will contain dates, not in order and some dates may be duplicated. In the "1st Date Feedback I'm getting the correct date of 12/12/25 with the formula: =MIN([Client Update Date]:[Client Update Date]) In the "2nd Date…
