Skip to Content
Author's profile photo Sreenivasulu Dasari
Sreenivasulu Dasari
June 10, 2015 1 minute read

Number of Business Days in a month (excluding weekends)

Hi AlI,

I am explaining how to calculate number of business days in a month (excluding weekends).

Below are the steps.

  • VD_Date = CurrentDate()

/wp-content/uploads/2015/06/1_722404.png

  • VD_FirstDayMonth

         =RelativeDate(LastDayOfMonth(CurrentDate());-ToNumber(Left(FormatDate(LastDayOfMonth(CurrentDate()) ;”dd/MM/yy”);2))+1)

/wp-content/uploads/2015/06/2_722567.png

  • VD_LastDayMonth

      =LastDayOfMonth(CurrentDate())

/wp-content/uploads/2015/06/3_722568.png

VD_NoBusinessDays

=DaysBetween(RelativeDate([VD_FirstDayMonth];Floor(DayNumberOfWeek([VD_FirstDayMonth])/6)*(8-DayNumberOfWeek([VD_FirstDayMonth])));RelativeDate([VD_LastDayMonth];Floor(DayNumberOfWeek([VD_LastDayMonth])/6)*(8-DayNumberOfWeek([VD_LastDayMonth]))))-(Floor(DaysBetween(RelativeDate(RelativeDate([VD_FirstDayMonth];Floor(DayNumberOfWeek([VD_FirstDayMonth])/6)*(8-DayNumberOfWeek([VD_FirstDayMonth])));(DayNumberOfWeek(RelativeDate([VD_FirstDayMonth];Floor(DayNumberOfWeek([VD_FirstDayMonth])/6)*(8-DayNumberOfWeek([VD_FirstDayMonth]))))+1)*-1);RelativeDate([VD_LastDayMonth];Floor(DayNumberOfWeek([VD_LastDayMonth])/6)*(8-DayNumberOfWeek([VD_LastDayMonth]))))/7)*2)/wp-content/uploads/2015/06/4_722569.png

Final Report as below.

/wp-content/uploads/2015/06/5_722573.png

Thanks,

Sreeni

Assigned Tags

      1 Comment
      You must be Logged on to comment or reply to a post.
      Author's profile photo Narashimman K S
      Narashimman K S

      Couple of points:

      • The results that you are showing is wrong. For example in your screenshot you got 21 business days for June(this month) - But it is should have been 22.
      • You are using a very complicated way to achieve what you want.
      • try to understand the logic applied below and test it to see if the results are good.

      I created separate variables for your understanding, merge them into one as needed. There could be more simpler ways around too.

      VD_FirstDayMonth= RelativeDate([Date];-1*DayNumberOfMonth([Date])+1)

      VD_LastDayMonth= LastDayOfMonth([Date])

      VD_TotalNumberDaysInMonth= DaysBetween([VD_FirstDayMonth];[VD_LastDayMonth])+1

      VD_DayNumberOfWeekFIrstDay= DayNumberOfWeek([VD_FirstDayMonth])

      VD_FirstWeekendDayNumber= If (7-[VD_DayNumberOfWeekFIrstDay])=0 Then (If [VD_TotalNumberDaysInMonth]=30 Then 2 Else 3) Else (7-[VD_DayNumberOfWeekFIrstDay])

      VD_NumberofWeekendDaysInMonth= If [VD_FirstWeekendDayNumber]+28<[VD_TotalNumberDaysInMonth] Then 10 Else (If [VD_FirstWeekendDayNumber]+27<[VD_TotalNumberDaysInMonth] Then 9 Else 8)

      Your result object,

      [VD_NumberBusinessDays]=[VD_TotalNumberDaysInMonth]-[VD_NumberofWeekendDaysInMonth]