List All Time Points within a Specific Period according to the Specified Interval

 

Problem description & analysis

Enter parameters of start and end dates, say from 2020/3/1 to 2021/3/30, to generate a sequence with a fixed interval of 31 days in Excel, as shown below:

A

1

2020/3/1

2

2020/4/1

3

2020/5/2

4

2020/6/2

5

2020/7/3

6

2020/8/3

7

2020/9/3

8

2020/10/4

9

2020/11/4

10

2020/12/5

11

2021/1/5

12

2021/2/5

13

2021/3/8

The task involves the generation of a time series.

Solution & explanation

Set start and end dates parameters in the format of yyyy/mm/dd in an esProc script, which are start=2020/3/1,end=2021/3/30:

Write the following cdoe in A1: =periods@x(start,end,31)

periods function generates a time series according to the specific start time and end time, the specified unit (default is day) and interval value; @x enables discarding the terminal time point.

Execute the esProc script, and copy and paste A1’s result to cell A1 in Excel to complete the computation.

Q & A Collection

https://stackoverflow.com/questions/63836377/want-to-list-dates-from-vba-code-write-start-date-and-end-date-and-increse-by-mo