Calculating the first weekday of the month

Saturday 12 May 2012 @ 8:34 am

One of my customers has reports scheduled to run every weekday. The rule is that all month long it should pull the dates for the current month. But once a month, at the beginning of the month, they want to run the entire previous month. So I wrote their selection formula to say “If today is the first of the month, then run LastFullMonth, otherwise run MonthToDate:

If Day (currentDate) = 1
then {DateField} in LastFullMonth
else {DateField} in MonthToDate

This worked fine for several months until we got to April.  Because the report only runs on weekdays, and because April first was a Sunday, the March report didn’t get run.  Nothing ran on Sunday, and the report that ran on Monday April 2 was for April.  So we needed to tweak the logic and calculate the first weekday of the month.  So I wrote a formula called “FirstWeekDay” that looked like this:

If DayOfWeek (Minimum (MonthToDate)) = 7 then 3 else
If DayOfWeek (Minimum (MonthToDate)) = 1 then 2 else 1

It returns a number from 1 to 3 which represents the first weekday of the current month.  Then I replaced the ‘1’ in the selection formula with my new formula field, like this:

If Day (currentDate) = {@FirstWeekDay}
then {DateField} in LastFullMonth
else {DateField} in MonthToDate

(For examples of my most popular formulas, please visit the FORMULAS page on my website.)

Leave a Reply

Recrystallize Pro