Calculating number of weekdays from end of month (or net work days - number of business days between two dates )

Hi - In my dataset, I have a date field for example: 2018-08-06.
I would like to add a column that lets me know whether that date was more than 2 business days from the previous month’s end 2018-07-31. I do not want to count weekends.

cc: @yuri - Do you happen to know this?

The most precise way to calculate the number of business days between two dates, including holidays, is to have a company-wide date dimension table. That is because different industries and different locales have geography and industry specific holiday.

But if we don’t need to worry about holidays but just get three business days, excluding the weekend, we can do the following.

myDate = 2018-08-06
DateTrunc(“month”,2018-08-06) // will give me start of the month, which is 2018-08-01
DateAdd(“day”, -1,DateTrunc(“month”,2018-08-06)) // will give me the end of the previous month, 2018-07-31

When is 3 business days from the end of the previous month:
If previous month end falls on a Monday or a Tuesday, then add 3 calendar days
Else If previous month end falls on Wed, Thu, Fri or Sat - then add 5 calendar days
Else If previous month end falls on Sunday - then add 4 calendar days

Sigma formula:
DateAdd(“day”, (In(Weekday(DateAdd(“day”, -1,DateTrunc(“month”,2018-08-06))),2,3,4), 3, In(Weekday(DateAdd(“day”, -1,DateTrunc(“month”,2018-08-06))),5,6,7), 5, 4), DateAdd(“day”, -1,DateTrunc(“month”,2018-08-06)))

There, I used this formula:
DateAdd(“day”, (In(Weekday([myPreMoEnd]),2,3,4), 3, In(Weekday([myPreMoEnd]),5,6,7), 5, 4), [myPreMoEnd])

Formula to calculate net working days/business days between dates (like Excel’s networkdays):

DateDiff(“day”, [Start Date], [End Date]) - (DateDiff(“week”, [Start Date], [End Date]) * 2) + If(Weekday([Start Date]) = 7 and Weekday([End Date]) = 7, 0, Weekday([Start Date]) = 7, 1, Weekday([End Date]) = 7, -1, 0)

1 Like

@garethbrickman I just used your formula - worked like a charm. Thank you!