How to setup an "If" with dates involved? (2024)

Mike Wilday ✭✭✭✭✭✭

08/23/18

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!

0 · Share on FacebookShare on Twitter

Marios Hadjikyriacou ✭

08/23/18

#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.

0 · Share on FacebookShare on Twitter

Mike Wilday ✭✭✭✭✭✭

08/23/18

Invalid operation errors occur because of the formula. Can you attacha 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

0 · Share on FacebookShare on Twitter

rfhickey ✭

05/06/19

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

I'm not sure how to proceed.

0 · Share on FacebookShare on Twitter

Andrée Starå ✭✭✭✭✭✭

05/06/19

Hi,

Can you share a screenshot and the formula?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

0 · Share on FacebookShare on Twitter

rfhickey ✭

05/06/19

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.

0 · Share on FacebookShare on Twitter

Mike Wilday ✭✭✭✭✭✭

05/06/19

Hi rfhickey, are both of those columns date-type columns? There seemsto 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

0 · Share on FacebookShare on Twitter

Andrée Starå ✭✭✭✭✭✭

05/06/19

Happy to help!

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

Let me know if I can help with anything else!

Best,

Andrée

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

0 · Share on FacebookShare on Twitter

rfhickey ✭

05/06/19

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

0 · Share on FacebookShare on Twitter

Mike Wilday ✭✭✭✭✭✭

05/06/19

0 · Share on FacebookShare on Twitter

lmckaig96366 ✭✭

08/21/19

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.

0 · Share on FacebookShare on Twitter

Mike Wilday ✭✭✭✭✭✭

08/26/19

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 yourcurrent 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-upconditional formatting to format the row red when the helper column = red.

0 · Share on FacebookShare on Twitter

Anna Makhina ✭✭✭✭

09/12/22

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

0 · Share on FacebookShare on Twitter

Mike Wilday ✭✭✭✭✭✭

09/12/22

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.

0 · Share on FacebookShare on Twitter

Anna Makhina ✭✭✭✭

09/16/22

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.

0 · Share on FacebookShare on Twitter