Hello,
I have a macro that I use on a weekly basis. In the macro I have a spot for an input date for the weekly analytics. This input date is always the previous Monday date, for example next week when I run the macro the input date will be 03/07/2022.
I also have an input date for analytics where I use five weeks of data. This input date is always the fifth Monday from the current Monday, for example next Monday the input date will be 02/07/2022.
Everytime I run the macro I have to update the input dates. Is there any way to assign these input dates to a varaible so I can automate this process?
I've attached the macro for reference. The weekly input date is on line 115, the 5 week input date is on line 161.
Any help would be appreciated.
klegendre
Offline
Last seen: 2 years 1 month ago
Joined: 03/10/2022 - 14:33
Hi klegendre,
If you are running this on a Monday and want the Monday one week in the past and five weeks in the future that is relatively easy. If you are running this on another day then the Monday then it is a bit more complex. For right now I am assuming you run this on a Monday and you want the date one week back and one week forward.
For one week back it would look like this:
previousMonday = Format(DateSerial(Year(Now()), Month(Now()), Day(Now()) - 7), "yyyymmdd")
which if you ran this on March 14, 2022 it would return 20220307
How it works is that you use the Year(), Month() and Day() with the Now() function to get the current date. You then place this into the DateSerial function which takes three integers as parameters, the first being the year, second is the month and final is the day. I then subtract 7 from day to obtain the date for the previous Monday. Finally I wrap it in the Format function so that it outputs as YYYYMMDD which is the IDEA default format but if you need it in a different format for your script you can just change the mask.
To get the five weeks in the future I do the same thing but add 35 (five weeks) to the day:
futureMonday = Format(DateSerial(Year(Now()), Month(Now()), Day(Now()) + 35), "yyyymmdd")
This would return 20220418.
Hopefully that makes sense.