How to show that two timestamps are identical?

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,344
Office Version
  1. 365
Platform
  1. Windows
One column of a table contains timestamps (date + time). Here is an example:

R/CAB
18/04/18 08:10p8/04/18 08:10p
28/05/18 06:47p8/05/18 06:47p
38/06/18 07:35a8/06/18 07:35a
48/06/18 06:03p8/06/18 06:03p
58/06/18 06:03p=
68/07/18 06:55p8/07/18 06:55p
78/07/18 06:55p=
88/07/18 06:55p=
98/08/18 08:46a8/08/18 08:46a
108/09/18 09:30a8/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.
 

Some videos you may like

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
Joined
Sep 21, 2017
Messages
7
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
Joined
Jun 12, 2014
Messages
46,530
Office Version
  1. 365
Platform
  1. Windows
If col A is numerical values, rather than text, then you could use CF
 
Last edited:

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,344
Office Version
  1. 365
Platform
  1. Windows
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/CCDEFG
511.00D5: =C52.00F5: =D5+1
60.99too smallD6: =C61.99F6: =D6+1
79999.00D7: =C7100.00F7: =D7+1
899.01too largeD8: =C8100.01F8: =D8+1

<tbody>
</tbody>

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

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,344
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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/CCD
1255
1377
1477
1533

<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
Joined
Jun 12, 2014
Messages
46,530
Office Version
  1. 365
Platform
  1. Windows
In CF > New Rule > use a formula > =COUNTIF(A$6:A6,A6)>1 > for format use "=" > OK
 

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,344
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jun 12, 2014
Messages
46,530
Office Version
  1. 365
Platform
  1. Windows
With the formula the A$6 needs to be the first cell in the applies to range
 

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,344
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Mar 11, 2015
Messages
6,770
Office Version
  1. 365
Platform
  1. Windows
@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:

Watch MrExcel Video

Forum statistics

Threads
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...
Top