How to conditionally format a row to show changes within the last 22 hours

Jim Balow

Hi all,

I am trying to come up with a way to highlight an entire row after a change has been made within the last 22 hours (not a full day).

I have a sheet that is reviewed and updated in a daily meeting, but want to set the stage for the next meeting by only highlighting rows that have changed since that meeting. I have attempted to pull the timestamp from modified and use it in a formula to validate the length of time, but have run into all rows "Modified" column getting updated due to the formula running everytime someone makes an update.

I saw that there are some options to copy rows over to a helper sheet, but wanted to see if this has been achieved by someone previously.

Thanks!

-Jim

JR90

So this method should work:

Create a column and the column type should be Modified Date

Create a helper column and you could call it time and the column formula should be

=IFERROR(TIME(RIGHT([MODIFIED DATE]@row , 8)), "")

This formula will pull the time out of the date modified column

Create a new rule under conditional formatting, here is a quick example I made

So any time that is less than 2:45 PM will be highlighted yellow, and you could change it to whatever time and have it highlight any cell you want.

Jim Balow

Hi JR90,

Thanks for the suggestion. This did not resolve the issue with the entire sheet updating all rows, and I believe it is due to the column formula.

From what I have tried, I think it will need to be a more robust work around where the row needs to get copied to another helper sheet and then linked back in, but I am not certain.

JGPM

See if you move your new conditional formatting to the top of the formatting rules list. See yellow highlight note about conditional format rule order.

Jim Balow

It's not a matter of the conditional format, I believe the problem is that the column formula updates all rows every time a change is made to a single row. So then it is showing that all rows have been modified when it is not the case.

JGPM

Our Modified row below has a different date / time for every line. I get why you need this to work!! Is this how your modified column is set up?

Are these children rows? Parent rows?

Perhaps try adding the Modified column again.

I hope you get your solution!

Jim Balow

I ran another test, after re-adding the Modified column. It is setup as shown, but the formula in the Time column is still updating the modified time and date for every row whether it has been actually modified.

JGPM

Are these children rows?

Are there predecessors in common for these rows?…so that something is being changed on one row and cascading to other rows.

Something in those rows must be changing. Any hidden columns that are getting a cascading change?