How to Build a 24 Month Rolling Window
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I have a series of month end dates that I need to turn into a rolling 24 month window. The date set looks like this:
Dates
1/31/2020
2/29/2020
3/31/2020
4/30/2020
5/31/2020
6/30/2020
7/31/2020
8/31/2020
9/30/2020
10/31/2020
11/30/2020
12/31/2020
1/31/2021
2/28/2021
3/31/2021
4/30/2021
5/31/2021
6/30/2021
7/31/2021
8/31/2021
9/30/2021
10/31/2021
11/30/2021
12/31/2021
My solution needs to look like this (easy enough, but see the conditions below):
Dates Outcome
1/31/2020 2020-01
2/29/2020 2020-02
3/31/2020 2020-03
4/30/2020 2020-04
5/31/2020 2020-05
6/30/2020 2020-06
7/31/2020 2020-07
8/31/2020 2020-08
9/30/2020 2020-09
10/31/2020 2020-10
11/31/2020 2020-11
12/31/2020 2020-12
1/31/2021 2021-01
2/28/2021 2021-02
3/31/2021 2021-03
4/30/2021 2021-04
5/31/2021 2021-05
6/30/2021 2021-06
7/31/2021 2021-07
8/31/2021 2021-08
9/30/2021 2021-09
10/31/2021 2021-10
11/30/2021 2021-11
12/31/2021 2021-12
How do I update the rows so that the last row is always derives from the current data (new months occur on a semi-random basis)? I how do I ensure that the 24 month window only reaches 2 years into the past?
So that when January of 1/2022 is added to the data set the result is:
Dates Outcome
2/29/2020 2020-02
3/31/2020 2020-03
4/30/2020 2020-04
5/31/2020 2020-05
6/30/2020 2020-06
7/31/2020 2020-07
8/31/2020 2020-08
9/30/2020 2020-09
10/31/2020 2020-10
11/31/2020 2020-11
12/31/2020 2020-12
1/31/2021 2021-01
2/28/2021 2021-02
3/31/2021 2021-03
4/30/2021 2021-04
5/31/2021 2021-05
6/30/2021 2021-06
7/31/2021 2021-07
8/31/2021 2021-08
9/30/2021 2021-09
10/31/2021 2021-10
11/30/2021 2021-11
12/31/2021 2021-12
1/31/2022 2022-01
Solved! Go to Solution.
- Labels:
- Date Time
- Developer Tools
- Iterative Macro
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
