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 there. I am working on several sheets that have a column called "Hours" which holds an hour estimate for task rows. I'd like for their parent rows to have a sum like "=sum(children())" so I can see how many hours are estimated for each project. My problem is that while I can manually set this for the rows, these sheets…
I am struggling finding a formula to mark the successor task "In Progress" when predecessor is "Complete" like can be done in MPP. I am encouraging my organization to move to SS with the automation, reporting, dashboard and automation capabilities, and away from MPPP so trying to replicate some of the items they utilize in…
HI All, I have a formula that i have got working - only issue i have is I have run out of cross reference "space" and cannot add my last spring to complete my function is there anther way to do this Source sheet OR date UP date Site Target sheet Required date Updated Date Site i need to return source sheet UP date, when OR…
