IF formula mix AND & OR functions, dates on modified and created columns

krazy@irl

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

Paul Newcome

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

krazy@irl

Great, thanks Paul!

Paul Newcome

Happy to help! 

krazy@irl

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?

Andrée Starå

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

krazy@irl

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?

krazy@irl

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

 

Andrée Starå

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

Paul Newcome

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?

Andrée Starå

Great thinking Paul but I suspect that the linking would update like before. I hope not!

Let me know your result!

Best,

Andrée

Paul Newcome

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.

Paul Newcome

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

krazy@irl

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.

krazy@irl

Also, i think your formulas are all referring to Today() even in the second group.

Paul Newcome

Ah. Makes sense. Yes. Changing to blank will still update the Modified column. Hmm...