Mario
Qrew Member
8 months ago

# Formula to determine working days in a fiscal month

I'm working on a summary report and I'm trying to count the number of weekdays in a fiscal month.  I have the formula working for the calendar month, but our fiscal month is always the 22nd of the current month to the 21st of the next month.  If there a formula that will capture this?

I basically need the formula to take [Scheduled Date], find which month it lands, and then count the weekdays between the 22nd to and the 21st.

Example:
Fiscal Jan is Dec 22nd - Jan 21.
Fiscal Feb is Jan 22 - Feb 21.
Fiscal March is Feb 22 - March 21
Etc

The formula I have is below, which works but only for calendar month.

var date FirstDay = FirstDayOfMonth([Scheduled Date]);
var date LastDay = LastDayOfMonth([Scheduled Date]);

WeekdaySub(\$LastDay, \$FirstDay) + 1

------------------------------
Mario
------------------------------
• Something like this should work:

var date t = [Scheduled Date];

WeekdaySub(

ToDate(Month(\$t) & "-21-" & Year(\$t)),

ToDate(Month(\$p) & "-22-" & Year(\$p))

)

------------------------------
Chayce Duncan
------------------------------

• That worked, thank you!

------------------------------
Mario
------------------------------