Show Buttons
Share On Facebook
Share On Twitter
Share On Google Plus
Share On Linkdin
Share On Reddit
Contact us
Hide Buttons

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

This tuto­r­ial will teach you how to get all the work­ing days or week­days in a spec­i­fied date range(excluding Sat­u­days and Sun­days), sim­ple but very use­ful when you have come up with a excel where you have to work with all the busi­ness work­ing days.

Exam­ple:

If the date range is men­tioned between 8-Jan-2014 to 20-Jan-2014 then work­ing days the  work­ing days will be

08-01-2014

Wednes­day

09-01-2014

Thurs­day

10-01-2014

Fri­day

13-01-2014

Mon­day

14-01-2014

Tues­day

15-01-2014

Wednes­day

16-01-2014

Thurs­day

17-01-2014

Fri­day

20-01-2014

Mon­day

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

Expla­na­tion:

Nav­i­gate through all the Dates using a loop, check each date’s day and print it , if it is either of ‘Sat­ur­day’ or ‘Sun­day’ then just ignore it, sim­ple right J

Out­put:

GetWeekDays OR GetWorkingDays

GetWeek­Days OR GetWorkingDays

 

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: