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:

Directions

1.   Open esProc and create a new file.

2.    Write the following code:

A1   Open the original data file and import all data as a table sequence.

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

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

Note: The display format of dates and datetime should be consistent with that of Excel in order to get the same date format as Excel. 

3.   Run the script and copy & paste A3’s result in B1 of Excel.

Q & A Collection

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

[Attachment]: data.zip