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).
Smartsheet Community - I AM STUMPED!! I am trying to do a crosssheet SUMIFS formula with 1 Column and 2 Rows of Data. I need to sum across {Weeks 1 to 104} Column to match : {Helper Column} = Baseline@row Vertical Rows {Month} needs to match Month@row, and Row {Year} needs to match Year@row I have tried SUMIFS, JOIN,…
Hello I am looking to create a Smartsheet formula that returns the following result: Column A "Created Date" - this column type is a Created date and show results with date and time Column B "Received Date" - this column would round up the "Created Date" and just show date, no time. If the "Created Date" was after 2:00pm…
Cant figure out why this is not working. It comes back NO MATCH. I have changed it to true and to 1. Nothing works. HELP
