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

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

  1. 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]

    (0) 

Leave a Reply