IF formula mix AND & OR functions, dates on modified and created columns
I'm looking to add a formula which will check the dates in modified and created columns and update the status column. If the modified date is today and is greater than the created date I want to populate the Status column with "updated", but if the modified date is equal to today and if the created date is also equal to today then the Status column should display text "New".
If neither of the fields match todays date then the Status should be blank.
I have something working for displaying "updated"
=IF(AND(DATEONLY(Modified822) = TODAY(), DATEONLY(Created822) < TODAY()), "Updated", "")
and also if modified and created both contain todays dates then display as "New"
=IF(AND(DATEONLY(Modified976) = TODAY(), DATEONLY(Created976) = TODAY()), "New", "")
But I'm not sure how to combine the two if someone could help
Comments
-
=IF(AND(DATEONLY(Modified@row) = TODAY(), DATEONLY(Created@row) < TODAY()), "Updated", IF(AND(DATEONLY(Modified@row) = TODAY(), DATEONLY(Created@row) = TODAY()), "New", ""))
To nest IF statements, you just need to put the next IF in the third section of the previous IF.
-
Great, thanks Paul!
-
-
So i implemented this yesterday and all was working as expected. However when I open up my sheet today the columns were appearing empty - this is the behaviour I am expecting but then all of sudden the modified date gets updated to todays date and the formula cell changes to "updated" once again.
I guess the modified date is updated today because of the formula switching from updated to blank.
Is there anyway way of ignoring a column for modified date? Or some way around this?
-
Hi,
It's probably because of the TODAY function in the formula.
Unfortunately, it's not possible to don't include a value for the modification column. The modification column is for the whole row.
Would it work if you checked the specific columns for changes instead?
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.
-
Thanks Andrée,
Checking specific columns for changes may work. So i could create a formula that would update if any value in the first 5 columns changes in a specific row? Then I could update the value in my formula column? How would I go about doing this?
-
I think i misread your last comment. You're stating to look for specific changes? as in values in other cells? This will not work either I have two date columns, and three text columns, I'm looking for some ability to flag if values in any of these five cells change on a specific row that I can mark in another cell as "updated".
-
That's a tricky one! I don't think it's possible at the moment.
Maybe it could work with the help of a third-party solution like Zapier. Would that be an option?
I'll ponder and get back to you if I think of something.
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.
-
I wonder... What if you changed the formula to look at a cell reference instead of the TODAY function? That cell reference would house today's date.
So in [Column Name]1 you would have the basic
=TODAY()
and in the formula you would replace TODAY() with
[Column Name]$1
The date in [Column Name]1 would trigger the Modified column for that row, but would it cause the other rows that are actually being tracked to update the Modified column? I am going to set up a test for this, but I won't have results until tomorrow. Has anyone else tested this before?
-
Great thinking Paul but I suspect that the linking would update like before. I hope not!
Let me know your result!
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.
-
I suspect it will too, but I figure it's worth a shot. At the very least we will have a little bit more knowledge about how exactly this TODAY function works.
-
Here's a published link (edit by anyone) to my testing sheet. The top portion is my control group. I used the same exact formula as above.
The bottom portion is my test group where I replaced TODAY with [Column2]$1.
If anyone has any other ideas, we can add them to this sheet as additional tests against the control today to see what happens tomorrow. My timezone is EST (Eastern Standard Time/East Coast United States).
-
Thanks Paul, I would suspect that this will still updated the modified field the next day too. Basically, I only want to show it as updated on the one day, the next day I want it to show again as blank, this is why we're checking for todays date, so even if todays date is referenced elsewhere it's going to update the value from "updated" to empty cell, then the modified date will update again and then the cell changes back to "updated", so will be stuck in this loop.
-
Also, i think your formulas are all referring to Today() even in the second group.
-
Ah. Makes sense. Yes. Changing to blank will still update the Modified column. Hmm...
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!