Calculation of Net Working Days

Here is a small VBA code for calculating net working days excluding Sunday:




Option Explicit


'Date should of "dd-mmm" format in cells

Function clcnetworkingdays(startdate As Date, enddate As Date) As Variant
Dim daystaken As Integer
Dim cnt As Integer, tempCounter As Integer
daystaken = enddate - startdate

    For cnt = 1 To daystaken
        If WorksheetFunction.Text((startdate + cnt), "ddd") = "Sun" Then
            tempCounter = tempCounter + 1
         
         End If
 
    Next
    daystaken = daystaken - tempCounter
    clcnetworkingdays = daystaken
End Function

Create Multiple Worksheet

Following VBA Code is an utility code for creating multiple worksheets  for a month on a daily basis:



Option Explicit

Sub createMultipleWorksheet()
Dim strdate As String
Dim numdays As Long, i As Long

Dim wsbase As Worksheet
On Error GoTo Errorhandler
    Do
        strdate = Application.InputBox("Please enter month and year:mm/yyyy", Title:="Month and year", Default:=Format(Date, "mm/yyyy"), Type:=2)
     
       If IsDate(strdate) Then Exit Do
       If MsgBox("Please enter a valid date such as ""01/2008"" " & vbLf & vbLf & "Shall we try again?", vbYesNo + vbExclamation, "Invalid date") = vbNo Then End
     
    Loop
    numdays = Day(DateSerial(Year(strdate), Month(strdate) + 1, 0))
 
    Set wsbase = Sheets("Sheet1")
    For i = 1 To numdays
        wsbase.Copy after:=Sheets(Sheets.Count)
        ActiveSheet.Name = Format(DateSerial(Year(strdate), Month(strdate), i), "mm.dd.yy")
    Next i
Exit Sub
Errorhandler:
MsgBox "Error" & Err.Description

End Sub