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 which can be checked.  As an example: 

For the month of May'22, the 3rd working day is 4th May.  If I have to add a holiday on 3rd May, the 3rd working day should be 5th instead of 4th. 

Looking forward to hearing from you.

RegardsPost
MC

------------------------------
MC Admin
------------------------------
Formulas and functions
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    3 years ago
    @MC Admin  I don't have a formula handy for that but I could work with you one on one to get it working.   Contact me by the email in my signature line if you like. Detecting holidays will need to use a table of holidays and a formula query.  We would also need to detect consecutive holidays if that is a possibility. ​

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------
  • 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
        ------------------------------