# 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.)