Forum Discussion

MCAdmin's avatar
MCAdmin
Qrew Member
3 years ago

Formula to Calculate 3rd working day of a month

Hello all,  I am looking for a formula which will help me determine 3rd working day of a month taking into account week-ends if any and list of public holidays. I have list of public holiday dates ...
Formulas and functions
PaulPeterson1's avatar
PaulPeterson1
Qrew Assistant Captain
3 years ago
You can put your holidays in a table, create a Boolean variable using a formula query to see if there is a holiday between the first weekday of the month and the third working day and use something like this.

var bool isHoliday = query text
var number dayOffset = If($isHoliday, 3, 2)

WeekdayAdd(ToWeekdayN(FirstDayOfMonth([Completion Date])), $dayOffset)

I know I have the holiday formula query around somewhere and can find it if you're interested in this approach.

------------------------------
Paul Peterson
------------------------------
PrashantMaheshw's avatar
PrashantMaheshw
Qrew Captain
3 years ago
PAUL holiday formula query would be greatly appreciated ! Please do post

------------------------------
Prashant Maheshwari
------------------------------
  • PaulPeterson1's avatar
    PaulPeterson1
    Qrew Assistant Captain
    3 years ago
    This is how I addressed the problem.  There may be something more elegant, but this is tested and does work.

    I put the holiday dates in a Holiday table.  I also added a formula text field for Holiday Observed:

    ToText(ToWeekdayN([Holiday]))​


    For the Third Work Day I used:

    var text tableID = "tid";
var text query = "{hoFID.GTE." & [Test Date] & "}";
var TextList dates = GetFieldValues(GetRecords($query, $tableID), hoFID);
var bool isHoliday = Contains($dates, ToText(FirstDayOfMonth([Test Date]))) or
    Contains($dates, ToText(WeekdayAdd(ToWeekdayN(FirstDayOfMonth([Test Date])), 1))) or
    Contains($dates, ToText(WeekdayAdd(ToWeekdayN(FirstDayOfMonth([Test Date])), 1)));
var number dayOffset = If($isHoliday, 3, 2);

WeekdayAdd(ToWeekdayN(FirstDayOfMonth([Test Date])), $dayOffset)


    where tid is the table ID for the Holidays table and hoFID is the field IF for Holiday Observed.

    The formula query will return a list of holidays that are on or after the Test Date field.  The isHoliday variable will check to see if the holiday observed date is in the first three working days of the month.  The dayOffset will return 3 if isHoliday is true and 2 if it is false.  

    I hope this helps.  Let me know if you have any questions.




    ------------------------------
    Paul Peterson
    ------------------------------

    • PaulPeterson1's avatar
      PaulPeterson1
      Qrew Assistant Captain
      3 years ago
      After posting, I had a thought about holidays like Christmas Eve, Christmas Day and New Years Eve, New Years Day and made the following modifications:

      var number holidays = If(Contains($dates, ToText(FirstDayOfMonth([Test Date]))), 1) +
    If(Contains($dates, ToText(WeekdayAdd(ToWeekdayN(FirstDayOfMonth([Test Date])), 1))), 1) +
    If(Contains($dates, ToText(WeekdayAdd(ToWeekdayN(FirstDayOfMonth([Test Date])), 2))), 1) +
    If(Contains($dates, ToText(WeekdayAdd(ToWeekdayN(FirstDayOfMonth([Test Date])), 2))) >0 and
        Contains($dates, ToText(WeekdayAdd(ToWeekdayN(FirstDayOfMonth([Test Date])), 3))), 1);
var number dayOffset = 2 + $holidays;​


      ------------------------------
      Paul Peterson
      ------------------------------
      • PrashantMaheshw's avatar
        PrashantMaheshw
        Qrew Captain
        3 years ago
        Thanks Paul !

        ------------------------------
        Prashant Maheshwari
        ------------------------------