How to show that two timestamps are identical?

[JenniferMurphy]

One column of a table contains timestamps (date + time). Here is an example:

R/C	A	B
1	8/04/18 08:10p	8/04/18 08:10p
2	8/05/18 06:47p	8/05/18 06:47p
3	8/06/18 07:35a	8/06/18 07:35a
4	8/06/18 06:03p	8/06/18 06:03p
5	8/06/18 06:03p	=
6	8/07/18 06:55p	8/07/18 06:55p
7	8/07/18 06:55p	=
8	8/07/18 06:55p	=
9	8/08/18 08:46a	8/08/18 08:46a
10	8/09/18 09:30a	8/09/18 09:30a

<tbody>
</tbody>

A5 is identical to A4 and both A7 & A8 are identical to A6. I would like to display the data in Col A as it is in Col B. Is that possible without changing the actual underlying values?

I would prefer not to have a second column just for the display, as above.

 

[JenniferMurphy]

@JenniferMurphy

A simple way to prove it works... follow EXACTLY...

It works! Amazing.

I did it on this table using this procedure:

• Select D4:D11
• Enter the rule as "=D3=D4"

R/C	C	D
4	08/27/18 10:16	08/27/18 10:16
5	08/27/18 10:17	08/27/18 10:17
6	08/27/18 10:17	=
7	08/27/18 10:18	08/27/18 10:18
8	08/27/18 10:19	08/27/18 10:19
9	08/27/18 10:19	=
10	08/27/18 10:19	=
11	08/27/18 10:16	08/27/18 10:16

<tbody>
</tbody>

But a couple of details about your procedure bothered me, so I redid it on another sheet like this:

• Select D5:D11 (all but first row)
• Enter the rule as "=D5=D4" (comparing up seems more natural to me)

This seems more logical to me and it appears to get the same result.

Is there anything about my procedure that could bite me?

I then copied the entire table to a new location below the old one. The rules were also copied and continued to work.

I then tried adding rows to the end of the table and the rules expanded to include those rows and continued to work.

So all appears to be joy and light. Maybe this will give me courage to try using conditional formatting again.

Thank you!!!

 

[JenniferMurphy]

These CFs are working great. So, again, my thanks to Yongle and everyone else who contributed.

After having worked with these for awhile, I have a question about the different ways to code a cell address. I understand that "A1" is a relative address while "$A$1" is an absolute address. But I am not sure I fully understand how this works in a CF rule.

Consider this table of dates.

R/C	C	D
6	Sun 08/05/18 06:47p	Mon 08/06/18 12:02a
7	Mon 08/06/18 07:35a	Mon 08/06/18 05:21p
8	Mon 08/06/18 06:03p	Mon 08/06/18 07:45p
9	Mon 08/06/18 06:03p	Mon 08/06/18 07:45p
10	Tue 08/07/18 06:55p	Tue 08/07/18 03:33p
11	Tue 08/07/18 06:55p	Tue 08/07/18 08:24p
12	Tue 08/07/18 06:55p	Tue 08/07/18 08:24p
13	Wed 08/08/18 08:46a	Tue 08/07/18 08:24p

<tbody>
</tbody>

If I apply the CF rule "=C6=C7" with Custom formatting of "= " to the range "$C$6:$C$13", I get:

R/C	C	D
6	Sun 08/05/18 06:47p	Mon 08/06/18 12:02a
7	Mon 08/06/18 07:35a	Mon 08/06/18 05:21p
8	=	Mon 08/06/18 07:45p
9	Mon 08/06/18 06:03p	Mon 08/06/18 07:45p
10	=	Tue 08/07/18 03:33p
11	=	Tue 08/07/18 08:24p
12	Tue 08/07/18 06:55p	Tue 08/07/18 08:24p
13	Wed 08/08/18 08:46a	Tue 08/07/18 08:24p

<tbody>
</tbody>

If I apply a similar rule over the range "$C$6:$D$13", I get:

R/C	C	D
6	Sun 08/05/18 06:47p	Mon 08/06/18 12:02a
7	Mon 08/06/18 07:35a	Mon 08/06/18 05:21p
8	=	=
9	Mon 08/06/18 06:03p	Mon 08/06/18 07:45p
10	=	Tue 08/07/18 03:33p
11	=	=
12	Tue 08/07/18 06:55p	=
13	Wed 08/08/18 08:46a	Tue 08/07/18 08:24p

<tbody>
</tbody>

But I can get this same result with one rule by applying "=C6=C7" to the entire range "$C$6:$D$13".

My understanding of this is that this rule is "compare each cell in the range to the one below it". So the range could be as wide as is appropriate. I don't need a separate rule for each column. Right?

Next, I tried the rule "=$C$6=$C$7". I got no "=" signs. I interpret this as "for each cell in the range, compare C6 to C7, and since that result never changes, I will either get all ="s or none. Right?

But support I made the rule something like "=$C6=$C7"? Does this mean "for each cell in the range, compare its counterpart in Col C to the next cell in Col C? That would mean that I would get rows of all ='s or none. Right.

Is there some general way to think of all of the 16 combinations of relative and absolute cell designations for this simple formula?

Thanks