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'm wondering if it is possible to join text via a formula in the sheet summary panel if certain criteria are met. For example, if the year is 2025 and the End Date is in the future, and Parent = "Real Estate Budget Items", I'd like to list both the Budget amount ant the End Date separated by a comma. In this…
I am working on a metrics formula. It worked when I wrote it, I was using the data in a graph, which worked, I changed the graph type, and suddenly the data went to zero. Now, all o my formulas set up like this are all returning a value of 0. Trying to determine what went wrong. I am attempting to count the number of…
Ok hive mind - I haven't been able to talk this formula out with someone yet, so I am looking for a little help. I have the formula that will fill out the children of my FINAL DATE column, but to fill out the parent row, I want my formula to look at the children of the NON REFUNDABLE DEPOSIT column, and tell me what is the…
