Is it possible to schedule a single automation to run bi-weekly? If so, it’s not obvious. Would appreciate anyone’s thoughts on how to accomplish running a report automation on a bi-weekly basis. The only way I see to accomplish this is to have two automations setup as Monthly (by week), each with a different week i.e., 1st Monday & 3rd Monday.
You could try a weekly scheduled bot that runs on Mondays with the following condition in the bot settings
OR(DAY(TODAY())<=7,
AND(DAY(TODAY())>14,
DAY(TODAY())<=21),
DAY(TODAY())>28
)
The expression part DAY(TODAY())<=7 will ensure that the bot runs on first Monday of every month.
The expression part AND(DAY(TODAY())>14, DAY(TODAY())<=21) will ensure that the bot runs on 3rd Monday of the very month.
The part DAY(TODAY())>28 will ensure that the bot runs on 5th Monday of the week if there is a 5th Monday in the month. You could suitably include or exclude that part depending on your requirement- if you just need 2 reports per month you could omit it. If you need a report on all odd Mondays of the month, you could include it.
Edit:
Another compact expression to try will be
MOD(CEILING(DAY(TODAY())/7.00),2)=1
Edit 2
You can test these expressions in a VC with an expression something like
IF(AND(
WEEKDAY([Date_Column])=2,
OR(DAY([Date_Column])<=7,
AND(DAY([Date_Column])>14,
DAY([Date_Column])<=21),
DAY([Date_Column])>28
)
), "1st of 3rd or 5th Monday", "Other Day"
)
It will return “1st of 3rd or 5th Monday” on 1,3, and 5th Monday. [Date_Column] is a date type column in the same table.
Similarly other expression you could test with an expression in a VC
IF(
AND(
WEEKDAY([Date_Column])=2,
MOD(CEILING(DAY([Date_Column])/7.00),2)=1),
“1st, 3rd or 5th Monday” , “Other day”
)
The part WEEKDAY([Date_Column])=2 in both the test expressions ensures the weekday is Monday. In bot setting it is not necessary because we can set day of the week in the bot.
Even-numbered weeks:
MOD(WEEKNUM(TODAY()), 2) = 0
Odd-numbered would be ... = 1.
See also:
Hi Steve,
Excellent. That expression is further compact.
I feel that since the WEEKNUM() starts from the beginning of the years and does not depend on months in between, the first and third Mondays would alternate at times between even and odd week numbers computed by WEEKNUM()
For example in 2026, as per my understanding, with WEEKNUM(), all 1 and 3rd Mondays compute odd week numbers for the months of January to March May and June of 2026, but in April, July and August 1st and 3rd Mondays compute in even week numbers.
Of course, I may be missing an obvious point in your suggestion. So, may I request your deeper insights.
It all comes down to the definition of “bi-weekly”: is it every two weeks, or twice a month? I interpret it as every other week, which isn’t bound by month start and end.
Hi Steve,
Thank you. I agree with you. if it is purely alternate week then I think WEEKNUM() is excellent option.
I mainly concentrated on the following statement by @MichaelN where he mentioned 1st and 3rd Monday of the month which indicates 1st and 3rd Monday of each month.