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.

 

[cliftonvilla]

Not sure how you can have a cell taking two different values i.e. the original date/time at some point being replaced by an '='. Surely you will lose the date/time as it is effectively overwritten? You could append the '=' on to the end of the date/time string and then change the font colour of the date/time bit to white. This would however place the '=' at the right hand edge of the cell - unless you alter the size of the date/time string to very small which would move the '=' over to the left a bit nearer the middle. You might then need to strip off the '=' from the date/time string depending on your requirements.

 

[Fluff]

If col A is numerical values, rather than text, then you could use CF

 

[JenniferMurphy]

Not sure how you can have a cell taking two different values i.e. the original date/time at some point being replaced by an '='. Surely you will lose the date/time as it is effectively overwritten? . . .

The cell wouldn't have two different values. I'm not trying to design a qubit. It would just display a different value. This is already possible with Excel's conditional data formatting sections.

Consider this example:

• Col C has the values.
• Col D has the exact same values, as shown in Col E, but formatted with this conditional formatting string: [<1]"too small";[>99]"too large";0.00.
• Col F has the same values as Col E plus "1", which clearly shows that the underlying value is still usable in arithmetic.

R/C	C	D	E	F	G
5	1	1.00	D5: =C5	2.00	F5: =D5+1
6	0.99	too small	D6: =C6	1.99	F6: =D6+1
7	99	99.00	D7: =C7	100.00	F7: =D7+1
8	99.01	too large	D8: =C8	100.01	F8: =D8+1

<tbody>
</tbody>

I was hoping for something like that but with a comparison to a different cell.

 

[JenniferMurphy]

If col A is numerical values, rather than text, then you could use CF

If you mean conditional formatting, I couldn't get it to work.

In D17 of this table,

R/C	C	D
12	5	5
13	7	7
14	7	7
15	3	3

<tbody>
</tbody>

I tried

[=D13]"=";general

but Excel changed it to

[=0]"=";General

Do you know a different way?

 

[Fluff]

In CF > New Rule > use a formula > =COUNTIF(A$6:A6,A6)>1 > for format use "=" > OK

 

[JenniferMurphy]

In CF > New Rule > use a formula > =COUNTIF(A$6:A6,A6)>1 > for format use "=" > OK

Aha -- that CF.

I have never been able to get that to work. I always end up with multiple "rules", many conflicting with each other. I'll give it another try.

Thanks

 

[Fluff]

With the formula the A$6 needs to be the first cell in the applies to range

 

[JenniferMurphy]

With the formula the A$6 needs to be the first cell in the applies to range

Yeah, I know, but for some reason it has never worked for me. I think part of the problem is that I tend to reorganize the tables, so the ranges grow and shrink and the rules don't keep up -- possibly because I code them wrong. One time I literally ended up with something like 40-50 rules. Part of the problem is that there are multiple ranges and I like to copy one range to start another one.

Anyway, I'll give it another try.

 

[Yongle]

@JenniferMurphy


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

Add a NEW worksheet (NO HISTORIC MESS!)
This formula in cell A2 and copy to A3 and A4
=NOW()
This formula in A5 and copy to A6 and A7
=NOW()+2
This formula In A8 copy to A9 and A10
=NOW()+3

Select A2:A10
Click on conditional formatting \ New Rule \ Use formula ....
Enter this formula
=A1=A2
Click on grey Format button \ Number \ Custom \ under "Type" enter = (the equals sign that you want displayed)

It looks EXACTLY like you wanted column A to look