Formula to Flag or Not Flag

kkath

Goal:      Flag when the due date is in the next 7 days

               No flag when the due date is BLANK or more than 7 days in the future

 

Two formulas that seem to work separately:  =IF(Due Date2 <= (TODAY() + 7), 1, 0)

                                                                       =IF(ISBLANK(Due Date2), 0)

 

Anyone know how I can combine these formulas to make both work? 

Mike Wilday

Try this. I also added brackets around the column name because there is a space there.

=IF(ISBLANK([Due Date]2), 0, IF([Due Date]2 <= TODAY(7), 1, 0))

kkath

I think that worked!  Now I just need to figure out where mine went wrong... Thank you very much! 

Mike Wilday

Glad I could help you out! 

eric.bakker87821

I am simply trying to Flag any row that the End Date is today or in the past.  What is the formula and how do I paste in in?

Please step by step me as I am a new user to smart sheets.

 

Thanks!

L_123

eric you should post a new thread for questions, otherwise there is a good chance your question will be missed. That said, your answer is below.

Solution:

=if([End Date]@row <= today(),1,0)

Breakdown:

An if statement checks a criteria if it is true or false, then has a return section for both possibilities

=if(criteria, true, false)

a basic example would be

=if(1<5,1,0)

if 1 is less than 5, return a 1.

If not, return a 0.

This formula would return a 1

 

Next is referencing cells. If there is a space in a column name, or the column name ends in a number, then the name has to be in hard brackets [ ]. some example cell references are below

[Test Data]1

Test1

Data5

[Column2]1

You notice there is a column reference first, then a row reference second. If we are going to always reference the same row that the formula is on, we can use the @row reference.

You mention you want to check against the current date. In smartsheet the formula to reference the current date is Today(). if we want to reference yesterday we can use Today(-1)  or even Today() - 1. But we don't need that for what you are talking about. 

For a flag column you can flag a cell by putting a 1 in it. unflagged is 0. So our return criteria is going to be a 1 and a 0

 

So let's put all of this together.

if statement

=if(Criteria,true, false)

Column Name with the criteria we want to check(with hard brackets because it has a space

=if([End Date]1

We are referencing the same row as the formula so we can use @row

=if([End Date]@row

We want to know if it is today or in the past 

=If([End Date]@row <= today()

Now that we have our criteria built out, we need to build our return values. We want to flag when our criteria is true, so we are going to return a 1 in the true section of the if statement.

=If([End Date]@row <= today(),1

We could stop at this point, and the formula would work. It is a good habit to get into for smartsheet (and many other programs) to finish the formula even when you don't necessarily have to.

=If([End Date]@row <= today(),1,0

In Smartsheet, you don't have to add the ending parenthesis at the very end of the formula. The program will count them and add them in if you don't have any issues with your formula. As such, I recommend never typing in the last ending parenthesis. It can really help with troubleshooting the equation.

 

 

 

 

Mike Wilday

Sure, this is rather easy. The First thing you have to do is create a flag Symbol Column. 

1. Right click on the headers where you want the column to appear.
2. Choose insert column, and choose the Flag Symbol Column

Then you have to run an IF formula to determine if the date is in the past or today. You'll do this by comparing the end date to today()

1. Insert the following formula in the first row. =IF([End Date]@row <= Today(), 1, 0) 
2. This is determining if the end date on the current row where the formula live is less than or equal to today. Flags are created by inserting a 1, and 0 means no flag. 
3. After inserting the formula, then click on the cell, and drag the dark corner of the cell down to fill in all of the rows. 

Hope that helps! 

Mia

Hello, may i ask how to flag today's SUBTASK with a flag? I don't want to show the parent row.