python - How to find missing data based on timeframe (Date and Hour) in excel?

Question

I have quite large time-series data from 2000 - 2020 by hourly. But the problem is I have been stuck in preparing the data. I want to look for missing data and want to calculate how many percentages of the missing data.

The data will be like this

Location	Date	Data_1	Data_2
ABC	01/01/2000 00:00	20	4
ABC	01/01/2000 01:00	20	4
ABC	01/01/2000 02:00	20	4
ABC	01/01/2000 05:00	20	4
ABC	01/01/2000 06:00	20	4

question from:https://stackoverflow.com/questions/65896423/how-to-find-missing-data-based-on-timeframe-date-and-hour-in-excel

Answer 1

Create a table of all the valid times between 2000-01-01 and 2020-12-31. To do this, format column B as yyyy-mm-dd hh:mm.

In cell B1, input the first time (2000-01-01 00:00).

In cell B2 input the formula =B1+1/24.

Copy the formula in B2 down about 180,000 times Then do a VLOOKUP or similar to your source data.