Forum Discussion

Mario's avatar
Mario
Qrew Member
12 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
------------------------------
  • ChayceDuncan's avatar
    ChayceDuncan
    Qrew Captain
    12 months ago

    Something like this should work:

    var date t = [Scheduled Date];
    var date p = AdjustMonth($t,-1);


    WeekdaySub(

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

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

    )



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

    • Mario's avatar
      Mario
      Qrew Member
      12 months ago

      That worked, thank you!



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