# How to show that two timestamps are identical?

#### JenniferMurphy

##### Well-known Member
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.

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### cliftonvilla

##### New Member
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

##### MrExcel MVP, Moderator
If col A is numerical values, rather than text, then you could use CF

Last edited:

#### JenniferMurphy

##### Well-known Member
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

##### Well-known Member

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
Code:
``[=D13]"=";general``
but Excel changed it to
Code:
``[=0]"=";General``
Do you know a different way?

#### Fluff

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

#### JenniferMurphy

##### Well-known Member

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

##### MrExcel MVP, Moderator
With the formula the A\$6 needs to be the first cell in the applies to range

#### JenniferMurphy

##### Well-known Member
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. :banghead:

#### Yongle

##### Well-known Member
@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

Last edited:

Replies
11
Views
57
Replies
3
Views
351
Replies
3
Views
236
Replies
3
Views
183
Replies
2
Views
226

1,109,333
Messages
5,528,090
Members
409,801
Latest member
Jamaira

### This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
• Auto-Create a monthly Sign in sheet for preschool students
The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
• VBA vlookup multiple results
Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
• Extract values for earliest/latest times
I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...