Removing Duplicates with Timestamp

Andy0311

Board Regular
Joined
Oct 16, 2019
Messages
90
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.
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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:

Andy0311

Board Regular
Joined
Oct 16, 2019
Messages
90
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I get only the same number. Same result.
 

SuzB17

New Member
Joined
Oct 17, 2019
Messages
5
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!
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

Removed as on testing SuzB17 appears to be correct.
 
Last edited:

Andy0311

Board Regular
Joined
Oct 16, 2019
Messages
90
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 

Andy0311

Board Regular
Joined
Oct 16, 2019
Messages
90
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

I'm trying it soon. I'm sure it will work. Thanks again.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,975
Office Version
  1. 365
Platform
  1. Windows
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.
 

Andy0311

Board Regular
Joined
Oct 16, 2019
Messages
90
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Worked like a charm! Thanks again.

A
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,975
Office Version
  1. 365
Platform
  1. Windows
Curious, which suggestion ended up working for you?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,505
Messages
5,602,062
Members
414,498
Latest member
jordanmiller7890

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
Top