Be the first user to complete this post

  • 0
Add to List

VBA-Excel: Get all the WeekDays or Working days in Specified Date Range, (excluding Satudays and Sundays)

This tutorial will teach you how to get all the working days or weekdays in a specified date range(excluding Satudays and Sundays), simple but very useful when you have come up with a excel where you have to work with all the business working days.

Example:

If the date range is mentioned between 8-Jan-2014 to 20-Jan-2014 then working days the  working days will be

08-01-2014

Wednesday

09-01-2014

Thursday

10-01-2014

Friday

13-01-2014

Monday

14-01-2014

Tuesday

15-01-2014

Wednesday

16-01-2014

Thursday

17-01-2014

Friday

20-01-2014

Monday

Code :

Sub FnDateAdd()

   Dim strDate

   Dim mainWorkBook As Workbook

   Dim intCounter

   intCounter = 1

   Set mainWorkBook = ActiveWorkbook

   For i = Date To DateAdd("m", 1, Date)

  strDay = Format(i, "dddd")

     If strDay <> "Saturday" And strDay <> "Sunday" Then

     mainWorkBook.Sheets("Sheet1").Range("A" & intCounter) = i

     mainWorkBook.Sheets("Sheet1").Range("B" & intCounter) = strDay

     intCounter = intCounter + 1

  End If

Next

End Sub

Explanation:

Navigate through all the Dates using a loop, check each date’s day and print it , if it is either of ‘Saturday’ or ‘Sunday’ then just ignore it, simple right J

Output:

GetWeekDays OR GetWorkingDays
GetWeekDays OR GetWorkingDays



Also Read:

  1. VBA-Excel: Array Functions – IsArray()
  2. VBA-Excel: CurrentRegion
  3. Message Boxes in VBA Excel (Msgbox)
  4. VBA-Excel: Fill Excel Range Values in a 2D Array
  5. VBA-Excel: Date-Time Functions – FormatDateTime()
  6. VBA-Excel: Modified Consolidator – Merge or Combine Multiple Excel Files Into One Where Columns Are Not In Order
  7. VBA Excel - Cells, Ranges and Offset : Offset
  8. VBA-Excel: String Functions – Space()