How to setup an "If" with dates involved?

Marios Hadjikyriacou

Pretty new to the SS scene. I am looking for assistance/guidance on how to create a formula to check the current date, and compare it to another date in a different column and if the difference is greater than XXX days have it provide a symbol.

I have tried googling and have only found a bunch of functions:

https://help.smartsheet.com/functions

I am not too experienced with creating formulas so If anyone could provide any assistance or help it would be very appreciated.

Mike Wilday

We are doing something similar with an IF statement.

Here are steps to set up what you need if you're wanting to do a flag symbol column: 

1. Create a new flag symbol column
2. In the flag symbol column add the following formula:
   
   
   
   =IF([Date Column 1 Name]23 > [Date Column 2 Name]23, 1, 0) 
   
    
3. Then change the name of the column name to your actual column name and update the row (I used row 23 for example) to match the row you put the formula in. 

If your 1st date column is greater than the second date column you will get a flag. 

To update it for Red Amber Green symbols, then you can change the formula to 

=IF([Date Column 1 Name]23 > [Date Column 2 Name]23, "Red", "Green") 

Hope that helps! 

Marios Hadjikyriacou

#invalid operation

 

Don't know what I am doing wrong. It's confusing to me.

 

I have a hidden column for todays date and am having it compare to a deadline date. I am not sure what i am doing wrong.

Mike Wilday

Invalid operation errors occur because of the formula. Can you attach a screenshot of the columns you are using, and copy the formula into here that you are using. 

To simplify the formula, you can also use this one: 

For a flag symbol column: IF(today() > [insert name of due date column]23, 1, 0) 

For an RAG symbol column: IF(Today() > [insert name of due date column]23, "Red", "Green")

Update the row number from 23 to whatever row you are checking. You won't need the hidden column if you are checking against today(). 

Do be aware that Today() only updates the date when the sheet is opened.

https://help.smartsheet.com/articles/2476176-formula-error-messages#invalidoperation

rfhickey

I got the same thing as MHPVC when I follower these instructions: "invalid operation."

I'm not sure how to proceed.

Andrée Starå

Hi,

Can you share a screenshot and the formula?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

rfhickey

Hi, Andrée,

Thanks so much for your reply!

Please find attached a screenshot of the formula and columns that are giving me the "#INVALID OPERATION" message.

I appreciate your time.

 

Mike Wilday

Hi rfhickey, are both of those columns date-type columns? There seems to be issues with the comparison of those two columns. #invalid Operation error has to do with the operators. It appears those columns are linked to date columns, but I am wondering if those columns are actually date columns themselves. 

• https://help.smartsheet.com/articles/2476176-formula-error-messages#invalidoperation

Andrée Starå

Happy to help!

I saw that Mike answered already and I also think that's the issue!

Let me know if I can help with anything else!

Best,

Andrée

rfhickey

Thanks, Mike!

They were both date-type columns, yes.

I have had some luck using a different approach as detailed here: https://community.smartsheet.com/comment/163846#comment-163846

Mike Wilday

I would report this to Smartsheet Support. Report back here if they determine the cause. I imagine that this is a bug with countifs and linked cells. 

lmckaig96366

Hi Mike, do you know if there is a way to do something like this if you only have one date column? I'm trying to do something similar and am currently thinking of two options, preference being option 2 if it's possible. 

1) create some sort of if statement off of the date column and have it flag if more than three days pass before anything is entered into the second, non-date column.

      1.1) even if it flagged after three days independent of what is or isn't entered into the second column, that would work though is less desirable. 

2) create a formula to read the date entered into the date column compared against when something is entered into the second, non-date column. Here, we are more interested in how long it is taking from a date being entered and that line being addressed (something entered into the non-date column) If there is a formula to call out how many days have passed, I can use conditional formatting to highlight anyone who is taking more than three days to get something entered into the non-date column. 

Mike Wilday

One solution I can think of is to do an IF statement that checks if the field is blank and the date is more than three days past your current date. 

Create a helper column that is a RGB symbol column. 

Add this formula to the helper column. 

=IF(AND(ISBLANK([Non Date Column]@row), TODAY() > [Date Column]@row + 3), "Red", "Green")

Then set-up conditional formatting to format the row red when the helper column = red. 

Anna Makhina

https://community.smartsheet.com/discussion/comment/97081#Comment_97081

Good afternoon! I am looking for a formula to get the flag when my 2nd date column is greater or less than the first date column. Perhaps, you will be able to help? Thank you in advance.

Bet regards,

Anna Makhina

Mike Wilday

Try the following formula.

=IF([name of date column 2]@row > [name of date column 1]@row, 1, 0)

You will need to substitute the "name of date column" with the actual names of your columns.

Anna Makhina

Thank you Mike,

I used this formula =IF([Approved Ship Date to XT00]1 <> [Ship Date to XT00]1, 1, 0)

It works in both directions!

Do you probably know if there is a way to get this formula in a cell as soon as new lines are added? In case many people work with the same sheet, some could forget to copy formula to newly added lines.

Working with this formula I set a symbol (star) for any raw with mismatching dates and use conditional formatting for visualization. As soon as the different dates are checked by a person in charge, the star is removed- the mismatching dates aren't highlighted any longer.

Issues / possibilities:

1). It could be an option to drag formula till the end of the sheet but it seems to impossible to do so at once for 5000 lines). It means that occasionally users should check carefully if formula is still at place. Not sure it will work for everyone.

2). Its difficult to keep formula in cells: as soon as symbol (star) is removed, formula disappears. Users should pay attention to get formula copied into new cell, probably in some cased formula should be modified etc.

3) I have locked the (star) column with formula. I think in this case collaborators with the limited access will be able to remove the star in addition to formula. It would be great to have a possibility for Owner or Admin to see a sheet as Viewer before sharing permission levels. Not sure if this option is available at the moment.

Picture1.png