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

------------------------------
------------------------------

### 6 Replies

• @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
Qrew Assistant Captain
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)

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

------------------------------
Paul Peterson
------------------------------
• PAUL holiday formula query would be greatly appreciated ! Please do post

------------------------------
Prashant Maheshwari
------------------------------
• PaulPeterson1
Qrew Assistant Captain
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
var number dayOffset = If(\$isHoliday, 3, 2);

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
------------------------------