Spreadsheet: 685093d1593711735-working-out-if-data-is-regularly-periodic-excel-example
Sheet: Sheet1

 | Probem I am trying to address
Input | 2019-01-01 00:00:00 | 2019-02-01 00:00:00 | 2019-03-01 00:00:00 | 2019-04-01 00:00:00 | 2019-05-01 00:00:00 | 2019-06-01 00:00:00 | 2019-07-01 00:00:00 | 2019-08-01 00:00:00 | 2019-09-01 00:00:00 | 2019-10-01 00:00:00 | 2019-11-01 00:00:00 | 2019-12-01 00:00:00 | 2020-01-01 00:00:00 | 2020-02-01 00:00:00 | 2020-03-01 00:00:00 | 2020-04-01 00:00:00 | 2020-05-01 00:00:00 | 2020-06-01 00:00:00 |  | Desired Output
Monthly | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 |  | 1 | (or monthly) |  |  |  |  | 1 | Monthly
Quarterly | 200 |  |  | 300 |  |  | 200 |  |  | 200 |  |  | 200 |  |  | 200 |  |  |  | 3 | (or quarterly) |  |  |  |  | 2 | Bimonthly
Quarterly (offset) |  |  | 200 |  |  | 300 |  |  | 200 |  |  | 200 |  |  | 200 |  |  | 200 |  | 3 | (or quarterly)
Semi-Annually | 400 |  |  |  |  |  | 400 |  |  |  |  |  | 400 |  |  |  |  |  |  | 6 | (or semi-annually)
Semi-Annual (offset) |  |  |  |  | 400 |  |  |  |  |  | 400 |  |  |  |  |  | 400 |  |  | 6 | (or semi-annually) |  |  |  |  | 3 | Quarterly
Annual | 600 |  |  |  |  |  |  |  |  |  |  |  | 600 |  |  |  |  |  |  | 12 | (or annual) |  |  |  |  | 4 | Quadrimesterly
Monthly but one small deviation | 200 | 200 | 200 | 200 |  | 400 | 200 | 200 | 200 | 200 | 200 | 200 | 200 | 200 | 200 | 200 | 200 | 200 |  | N/A or No regular period |  |  |  |  |  | 6 | Semi-Annually
Monthly swapping to Quarterly | 200 | 200 | 200 | 200 | 200 | 200 | 600 |  |  | 600 |  |  | 600 |  |  | 600 |  |  |  |  |  |  |  |  |  | 12 | Annually
No Regular Period | 200 |  | 200 |  |  |  |  |  | 200 |  | 200 |  |  |  |  | 200 | 200
 | The Opposite: Working out regular entries given period (EASY!)
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Input
Output | 2019-01-01 00:00:00 | 2019-02-01 00:00:00 | 2019-03-01 00:00:00 | 2019-04-01 00:00:00 | 2019-05-01 00:00:00 | 2019-06-01 00:00:00 | 2019-07-01 00:00:00 | 2019-08-01 00:00:00 | 2019-09-01 00:00:00 | 2019-10-01 00:00:00 | 2019-11-01 00:00:00 | 2019-12-01 00:00:00 | 2020-01-01 00:00:00 | 2020-02-01 00:00:00 | 2020-03-01 00:00:00 | 2020-04-01 00:00:00 | 2020-05-01 00:00:00 | 2020-06-01 00:00:00 |  | Period | 3 |  | Value | 200
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Period | 6 |  | Value | 400
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Period | 12 |  | Value | 600
A Possible (if inefficient) solution…
Set up a counter row which counts the empty cells between two values, and resets the counter once a new cell with imputed value is found…
Monthly
Quarterly
Quarterly (offset)
Semi-Annually
Semi-Annual (offset)
Annual
Monthly but one small deviation
Monthly swapping to Quarterly
No Regular Period
Then determine the maximum that the counter reaches each time before it resets… |  |  |  |  |  |  | …And finally consider what variance this stream has. If the period is consistent, then the counter reaches the same number each time, and it has 0 variance
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | VAR | AVERAGE
Monthly
Quarterly
Quarterly (offset)
Semi-Annually
Semi-Annual (offset)
Annual
Monthly but one small deviation
Monthly swapping to Quarterly
No Regular Period
Monthly
Quarterly
Semi-Annually
Annual
No Regular Period
No Regular Period
Then take the next order difference, calculating only on the reset point. This gives and indication of the number of cells between values. Finally, calculate the variance of this flow. If the number of blank cells between entries is constant, then variance will be zero.
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | VAR | DIV
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | --->
Monthly |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | --->
Quarterly |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | --->
Semi-Annually |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | --->
Annual |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | --->
No Regular Period |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | --->
No Regular Period