0

# How to calculate the business day of the month

I need to be able to calculate the business day of the month. I have tried the following formula:

```Date_Time.Date.workday_seq - lookup(
Date_Time.Date.workday_seq,
Date_Time.Date.Date,
\$currentMonthStart
) + 1```

This works if there are no holidays between the beginning of the month and the current date. This month, however, Memorial Day (US) causes the count to go off by 1 after 5/25. For example, today, 5/29 should be the 20th business day. The formula evaluates to 21st.

How can I adjust for intermediary holidays between the first of the month and the current date? (I am sure Anurag Malik has some ideas!)

5replies Oldest first
• Oldest first
• Popular
• Looking at the date data, I notice the following:

Work Day Seq, Date, Day Name

11,538, 5/23/20, Saturday
11,538, 5/24/20, Sunday
11,539, 5/25/20, Monday <-- should this Work Day Seq have been increased?
11,540, 5/26/20, Tuesday

Like
• Hi Marc,

This Video shows how to  calculate number of business days in a month using Date dimension:

Like
• Anurag Malik Thank you, that is a start.  The base requirement is to get a business week ago equivalent to weekAgo for any given date.

Like
• Hi Marc, Are you looking for week ago business date?

•    7 business days ago from today for example

One way to solve this is create a new table as shown below and join this with date table:

Like
• Anurag Malik yes, that is what I am doing now. I have started a csv that contains that plus other meta-dates associated with each date.

Also, on a related note, I was starting with Date_US.csv as a base and noticed many rows of bad data starting around row 18631. I have corrected my copy, but it took a few hours to get it all right.

Like
Like Follow