Unique not removing duplicate values

[Resod2]

In Google Sheets, I have a table in which the headers should be all the unique start and end times that are in another table. However, when I use the Unique function, the result still contains duplicates. Does anyone know why this might be the case?

=TRANSPOSE(SORT(FILTER(UNIQUE(FLATTEN(B3:B,E3:E)),UNIQUE(FLATTEN(B3:B,E3:E))<>""),1,1))

As you can see there are two 04:00 and two 05:00s in the result.

Here's the link to a dummy spreadsheet with the problem: Generic Sheet

Any help would be greatly appreciated.

 

[Rijnsent]

Hi Resod2,
the main issue is a decimal calculation... What you see as 05:00 is in fact a decimal value: 5/24=0,041666666666666...*5. Computers are not the best calculating with decimals, so if, like in your case, you're removing the extra day in column E, the decimal part screws up a bit. Try e.g. =H2-I2 and set that cell to a decimal value, you'll see there is some minor decimal difference. The way to solve this is e.g. like so:
cell E3: =ARRAYFORMULA(IF(D3:D="","",ROUND(24*IF(D3:D>1,D3:D-1,D3:D))/24))
->basically: multiply by 24, round to a whole value and divide by 24.
Cheers,
Koen