Removing Duplicates with Timestamp

Andy0311

Board Regular
Joined
Oct 16, 2019
Messages
118
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hello,
I have a column of datestamps in a sheet and I need to separate the date from the time (=Int or =left both work). Then I copy the formula down. With the column highlighted, I go to Data and use the Remove Duplicates function. The result lists the distinct dates, but when I click OK, the dialog box closes and what remains is only the first date times the number of distinct days. For example, here is a column where I want to remove the duplicates. When I do that, in the column to the right, I do not get 9/17/2019, 9/18/2019, and 9/19/2019, but instead 9/17/2019 listed three times.

9/17/20199/17/2019
9/17/20199/17/2019
9/18/20199/17/2019
9/19/2019
9/17/2019

<tbody>
</tbody>


If I do the same operation with :
1
2
1
3


I get:
1
2
3

Does anyone know why that is happening? Thank you for your help.
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
With the original data (after the timestamp is removed) if you change the format of the cells to a number format rather than a date format what do you get returned?
 
Last edited:
Upvote 0
Hi - sorry if you know this already but it is linnked to the fact that the cells contain a formula not data. If you just list out the dates and use the remove duplicates, it works fine. It is only when the list uses the =LEFT formula to create it that the problem occurs. I'm not sure why but you can get around this by using an index formula to find unique values. {=INDEX($A$2:$A$6,MATCH(0,COUNTIF($B$1:B1,$A$2:$A$6),0))} (note it's an array formula). Might be a bit more long winded but works!
 
Upvote 0
Removed as on testing SuzB17 appears to be correct.
 
Last edited:
Upvote 0
Thank you so much! I will try this. Do you think using Text to Columns to separate the date portion from the timestamp would work. I will try the =Index. Thanks again.

A
 
Upvote 0
I'm trying it soon. I'm sure it will work. Thanks again.
 
Upvote 0
Do you think using Text to Columns to separate the date portion from the timestamp would work.
You can determine if it is date or text by using a formula like this (for an entry in cell A1):
=ISNUMBER(A1)
If it returns TRUE, it is valid date. If it returns FALSE, it is text.

If it is Text, then Text to Columns would probably work on this.

If a valid date, then you can use mathematical functions, like (for a value in cell A1):
Date piece: =INT(A1)
Time piece: =MOD(A1,1)
and use the appropriate formatting.
 
Upvote 0
Curious, which suggestion ended up working for you?
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,240
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top