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 have a project plan with a column called "Owner" that has a column type of contact list. The contacts are from multiple companies. It's a large project plan. I have created columns that have a column type of checkbox. I want to check the box if the owner is from a certain company (which can be determined by the…
Hello, I am trying to use JOIN-COLLECT to populate a cell with a contact by pulling from another sheet. However, when I use this formula it populates the cell with the name of the contact as a text string, but does not populate the cell with the contact itself. Both the column of cells being populated and the column the…
Hello Community, I need to create a report that shows capacity by month. My sheet tracks resource allocation on projects with resource start and end dates. (We can't afford the Resource Management module so we're doing it in Core SS). I've been asked to do a "heat map" of sorts where each column is a month. For each row…
