If a date is within 7 days of todays date

Carolyn Hill

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

 

J. Craig Williams

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

Travis

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.

Jim Hook

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.

J. Craig Williams

Carolyn,

 

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.

 

Craig

Carolyn Hill

Hi 

 

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?

Thank you

Carolyn

Carolyn Hill

Scrap that I worked it out, thank you very much all working.

 

Carolyn

Carolyn Hill

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?

 

Thank you

Carolyn

J. Craig Williams

Yes.

 

=IF([Date1]23 > [Date2]23, "yes", "no")

 

for row 23.

 

Craig 

Carolyn Hill

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.

 

Thank you

Carolyn

Jim Hook

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.

Tamara K

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)

Tamara K

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))

 

 

J. Craig Williams

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.

 

Hope this helps.

 

Craig

 

*Long story.

Tamara K

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.

J. Craig Williams

Tamara,

 

Even smart people make mistakes, no judging from me.

 

Thank you for the compliment.

 

Craig