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
Carolyn,
Try this:
Date is in the past?
I assume the date field is [Due Date]
Create a column "Today-7".
In this column, use this formula:
=IF((Today() - [Due Date]23) - 7 > 0, "older", "within 7 days")
for row 23.
Add a conditional highlight when this value is "within 7 days"
Note that this gives a false positive for Due Dates in the future.
If this is what you are looking for, a small tweak will get rid of that false positive.
There's a longer more complicated version here, for Due Dates in the future.
See more at: https://community.smartsheet.com/discussion/conditional-formating-between-2-dates#sthash.X6t5CbkV.dpuf
Hope this helps.
Craig
Carolyn, there is also a built in option with conditional formatting for "is in the last (days)". This can be used to identify any date in a given column that is in the last 7 days, then apply a format to the cell or row.
To set this up, open conditional formatting, create a new rule, select the date column and under Select criteria, choose "is in the last (days)" and add 7.
I use the way Travis outlined frequently. In some complex cases I have had to do something like Craig suggests to make creating the conditional formating rule easier when other sections of my sheet have data that would result in unwanted formatting.
Yes, Travis's way is the right one. I was so deep in a different but similar harder problem, I forgot about the easy solution.
That's a timely reminder for me.
Thank you for all of your suggestions. I have tried to do as Travis has suggested but do not see an option "is in the last ... days" - Assuming I'm missing something here?
Carolyn
Scrap that I worked it out, thank you very much all working.
Sorry I have another question;
Is it possible to set a condition that if a date in one cell is greater than a date in another cell that it will state yes or no in another cell?
Yes.
=IF([Date1]23 > [Date2]23, "yes", "no")
Great thanks, how do I enter that as when I click conditional formatting I have to use the drop down boxes, I dont have the option to enter the IF statement as you've described.
The conditional formatting needs to work on a cell that has already compared today's date against the date in the column you want to highlight. Use Craig's IF formula in another column to decide if the date compare is a yes or no. Then, go into conditional formatting and cause it to format the date in question with a fill color if the column that sets the condition is "yes", for example. You are correct that there is no way to put a normal formula in the conditional formatting rule.
I created one with this formula that works great to turn on a flag (Thanks again, Craig!):
=IF((TODAY() - [Due Date 1]2) - 14 > 0, 1, 0)
Then, when I tried to get fancy and create one that checks multiple due dates (If ANY of 6 due dates are within the next 14 days or in the past, it should turn on the flag for that row), I'm getting an error. Can someone hep me identify my error below? Thanks!
Here's my incorrect formula that is supposed to check the first 2 due dates...that's as far as I got.
=IF(OR(TODAY() - [Due Date 1]2) - 14 > 0, (TODAY() - [Due Date 2]2 - 14 > 0), 1, 0)
I'm pretty impressed w/myself...I figured it out!!
Here's the formula in case anyone else can benefit:
=(IF(OR(TODAY() - [Due Date 1]2 - 14 > 0, TODAY() - [Due Date 2]2 - 14 > 0, TODAY() - [Due Date 3]2 - 14 > 0, TODAY() - [Due Date 4]2 - 14 > 0, TODAY() - [Due Date 5]2 - 14 > 0, TODAY() - [Due Date 6]2 - 14 > 0), 1, 0))
Tamara,
Next time, please post the error message too - #INVALID DATA TYPE vs #UNPARSEABLE for example reveal possible causes that mke it easier to pinpoint the problem.
Luckily, I'm cursed*, so your problems are
1. paranthesis - make sure they close and do what you want them to do
2. (today - due date) will be positive for past and negative for future, -14 for 2 weeks from now. Your math is wrong by 4 weeks, simplied version below
=IF(OR((TODAY() - [Due Date 1]2) > - 14, (TODAY() - [Due Date 2]2) > - 14 ), 1, 0)
syntax:
=IF( OR( (date-comparison1) > -14 , (date-comparison2) > -14 ) , true, false )
Be aware that a blank due date will result in a true value.
If that is an issue, your formula is going to be long or you'll need to split it into several new columns.
*Long story.
Darn! You are so right with that 4-week mistake...and I thought I was so smart.
Thanks for saving the day, Craig. You're kind of my Smartsheet hero.
Even smart people make mistakes, no judging from me.
Thank you for the compliment.
Hi all. I'm trying to do something that I'm not sure if it's possible or not. We have a sheet that we are importing where the first column lists areas within our factory. Each column after that has a column name of a date. So 12/15/2025, 12/16/2025, 12/17/2025, etc. In each of the date columns is a target production…
Hello! I have what is probably a simple question my brain is just not comprehending properly. I need to SUMIFS from a sheet that matches some specific criteria but one of the fields I am using against needs to calculate a total if one dropdown field matches ANY ONE of these 3 items that is in it to be chosen… Capitol…
Hello, I have a checkbox column that I would like to be checked when comparing 2 columns. I would like it to be checked if the 2 columns are not equal or if one of the 2 columns has an error. It's important to note that both columns are numbers. My original formula was: =IF([COLUMN A]@row <> [COLUMN B]@row , 1, 0) However,…
