Removing Duplicates with Timestamp

[Andy0311]

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.

[TABLE="class: outer_border, width: 700, align: center"]
<tbody>[TR]
[TD]9/17/2019[/TD]
[TD]9/17/2019
[/TD]
[/TR]
[TR]
[TD]9/17/2019[/TD]
[TD]9/17/2019[/TD]
[/TR]
[TR]
[TD]9/18/2019[/TD]
[TD]9/17/2019[/TD]
[/TR]
[TR]
[TD]9/19/2019[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9/17/2019[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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.

 

[MARK858]

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?

 

[Andy0311]

I get only the same number. Same result.

 

[SuzB17]

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]

Removed as on testing SuzB17 appears to be correct.

 

[Andy0311]

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]

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

 

[Joe4]

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]

Worked like a charm! Thanks again.

A

 

[Joe4]

Curious, which suggestion ended up working for you?