Find Number of Days in a month using VBA (Excel 2007)

Calculate Number of Days in a Month

EOMONTH returns the serial number for the last day of the month that is the indicated number of months before or after start_date. Use EOMONTH to calculate maturity dates or due dates that fall on the last day of the month.

Function No_of_Days_in_Month()

Dim WrkMonths As Integer
Dim StartDate As Date
Dim EndDate As Date

StartDate = Now
EndDate = WorksheetFunction.EoMonth(StartDate, 0)

' if you want the days count
MsgBox "No of days in current month := " & Day(EndDate)


End Function

The above code will work only in Excel 2007

Find Last Day of the Month using VBA (Excel 2007)

Calculate Last Day of the Month

EOMONTH returns the serial number for the last day of the month that is the indicated number of months before or after start_date. Use EOMONTH to calculate maturity dates or due dates that fall on the last day of the month.

Function Last_Day_Of_the_Month()

Dim WrkMonths As Integer
Dim StartDate As Date
Dim EndDate As Date

StartDate = Now
EndDate = WorksheetFunction.EoMonth(StartDate, 0)

MsgBox "Last day of the current month is := " & EndDate

End Function

The above code will work only in Excel 2007

Calculate End Date of the Project using Excel VBA

Calculate End Date using Excel VBA

EDATE returns the serial number that represents the date that is the indicated number of months before or after a specified date (the start_date). Use EDATE to calculate maturity dates or due dates that fall on the same day of the month as the date of issue.

Function Get_The_EndDate()


Dim WrkMonths As Integer
Dim StartDate As Date
Dim EndDate As Date

StartDate = Now
EndDate = WorksheetFunction.EDate(StartDate, 3)

MsgBox "End Date of the Project is := " & EndDate

End Function


The above will work in Excel 2007 only

Get Net Working Days in a Year / Quarter using VBA

No of Working days in a Year / quarter using VBA

Many times we are confronted with a situation to estimate the days left in a quarter or year. Here is a where Excel 2007 has simplified that for us

Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays. Use NETWORKDAYS to calculate employee benefits that accrue based on the number of days worked during a specific term.

Function Get_Net_Working_Days()

Dim WrkDays As Integer
Dim StartDate As Date
Dim EndDate As Date

StartDate = Now
EndDate = #12/31/2008#
WrkDays = WorksheetFunction.NetworkDays(StartDate, EndDate)
Publish Post
MsgBox "No of Working Days Left := " & WrkDays

End Function

The function is exclusive in Excel 2007. There is no equivalent function in Excel 2003

Calculate Workdays - Excel VBA

Calculate the End date based on No. of Days using Excel Function / VBA

Most of the time you want to exclude weekends in the calculation for workdays, here is the simple way to do that.


This uses WORKDAY WorksheetFunction, which returns a number that represents a date that is the indicated number of working days before or after a date (the starting date). Working days exclude weekends and any dates identified as holidays. Use WORKDAY to exclude weekends or holidays when you calculate invoice due dates, expected delivery times, or the number of days of work performed.

Function Calculate_Workday()

Dim WrkDays As Integer
Dim StartDate As Date
Dim EndDate As Date

StartDate = Now
EndDate = WorksheetFunction.WorkDay(StartDate, 12)


End Function

The above excludes weekends and calculates the end date of the task based on the no. of days


Calculate the End date programmatically, Code Calculate Workdays - Excel VBA,