Skip to main content
Question

Formula to Calculate 3rd working day of a month

  • April 29, 2022
  • 0 replies
  • 250 views
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
------------------------------
This topic has been closed for replies.

MarkShnierYou
Forum|alt.badge.img+24
@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
------------------------------
Mark Shnier. Your Quickbase Coach
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
------------------------------
Terms & ConditionsAccessibility statement