Problem description

There is a column of datetime data in an Excel file, in which the datetime points are recorded by hour. However, the points are not exactly complete. Some points are repeatedly recorded such as 2020/1/6 1:00, and some are missing, such as 2020/1/6 4:00. Below is a part of the file:

We need to find dates covering a whole 24-hour period, that is, all datetime that includes points from 0 to 23, and enter the selected dates into column B. Below is the expected result:

Solution

Use SPL XLL plug-in

Write the formula in a blank cell:

=spl("=E(?).groups(E(date(E(#1)));icount(#1)).select(#2==24).(#1)",A1:A60)

As shown:

Explanation:

Get dates from values of the first column and group them, and then count the unique dates.

Get records whose count is 24 and select the first column.

Q & A Collection

https://stackoverflow.com/questions/63823770/check-hour-column-for-existence-of-all-hours

[Attachment]: data.zip