Be the first user to complete this post
|
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:
Also Read:
- VBA-Excel: String Functions – Space()
- VBA-Excel: Date-Time Functions – DateValue()
- VBA-Excel: Date-Time Functions – DateDiff()
- VBA-Excel: WorkBook.Save Method