Discussions

 View Only
  • 1.  Formula to Calculate 3rd working day of a month

    Posted 04-29-2022 05:33
    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
    ------------------------------


  • 2.  RE: Formula to Calculate 3rd working day of a month

    Posted 04-29-2022 09:31
    @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
    ------------------------------



  • 3.  RE: Formula to Calculate 3rd working day of a month

    Posted 04-29-2022 10:19
    Edited by Paul Peterson 04-29-2022 10:19
    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
    ------------------------------



  • 4.  RE: Formula to Calculate 3rd working day of a month

    Posted 04-29-2022 16:58
    PAUL holiday formula query would be greatly appreciated ! Please do post

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



  • 5.  RE: Formula to Calculate 3rd working day of a month

    Posted 04-30-2022 11:09
    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
    ------------------------------



  • 6.  RE: Formula to Calculate 3rd working day of a month

    Posted 05-01-2022 22:03
    Edited by Paul Peterson 05-02-2022 08:36
    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
    ------------------------------



  • 7.  RE: Formula to Calculate 3rd working day of a month

    Posted 05-04-2022 19:46
    Thanks Paul !

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