Using Date Functions of the WorksheetFunction Object in Excel 2010

  • Article

Office Quick Note banner

Handy Programming Tips for Microsoft Excel 2010: Learn how the date functions in the WorksheetFunction object can work for you in Microsoft Excel 2010.

Applies to: Excel 2010 | Office 2010 | VBA

In this article
Add the Code to the Visual Basic Editor
Test the Solution
Next Steps

Published:   May 2011

Provided by:    Frank Rice, Microsoft Corporation

The WorksheetFunction object has several date functions that you can use in your own applications. In this topic, you programmatically manipulate various date functions of the WorksheetFunction object. To complete this task, you must do the following:

  • Add the Code to the Visual Basic Editor

  • Test the Solution

Add the Code to the Visual Basic Editor

In this task, you add programming code that shows how to use the date functions of the WorksheetFunction object.

To add code to the Visual Basic Editor

  1. Start Excel 2010.

  2. On the Developer tab, click Visual Basic to open the Visual Basic Editor.

    Note

    If you do not see the Developer tab in Excel 2010, click the File tab, and then click Options. In the categories pane, click Custom Ribbon, select Developer, and then click OK.

  3. In the Projects pane, click Sheet1.

  4. Paste or type the following Microsoft Visual Basic for Applications (VBA) code into the module window.

    Sub TestWorksheetFunctionDates()
  ' Determine the date a specified number of workdays in the future:
  Dim newDate As Date

  ' Look at a calendar to verify the results.

  ' Calculate the date 10 work days from the current date.
  ' This does not take holidays into account:
  newDate = WorksheetFunction.WorkDay(#11/20/2011#, 10)
  Debug.Print newDate

  ' To take holidays into account, you can specify an array
  ' that contains a list of holiday dates, or you can specify
  ' a range that contains holiday dates. The ordering of the dates
  ' doesn't matter, but you must specify the full date, including
  ' the year. Obviously, you want to include all the holidays
  ' in your actual work calendar in this array or range:
  Dim holidays As Variant
  holidays = Array(#11/24/2011#, #12/25/2011#)
  newDate = WorksheetFunction.WorkDay(#11/20/2011#, 10, holidays)
  ' Once you take Thanksgiving Day in the US into account, ten
  ' work days pushes you out to Dec 5 2011:
  Debug.Print newDate

  ' What if your business doesn't use traditional weekends?
  ' The WorksheetFunction.Workday_Intl function enables you to specify
  ' not only holidays, but also specific weekend days. The default for the
  ' third parameter is 1 (Saturday/Sunday), but you can specify many
  ' predefined combinations of dates, or any custom combination. See the documentation
  ' for the Workday_Intl function for full details on the options:

  holidays = Array(#11/24/2011#, #12/25/2011#)
  ' Assume Saturday/Sunday weekends:
  newDate = WorksheetFunction.WorkDay_Intl(#11/20/2011#, 10, 1, holidays)
  Debug.Print newDate
  ' Use Sunday/Monday as weekend dates:
  newDate = WorksheetFunction.WorkDay_Intl(#11/20/2011#, 10, 2, holidays)
  Debug.Print newDate

  ' Use Friday through Monday as weekend dates. Note that the custom
  ' weekend date parameter includes 1 for each weekend date, and the string
  ' starts with Monday. In other words, specify 1 for weekend dates, 0 for
  ' work dates, in the format MTWTFSS. The string "1000111" represents
  ' weeked days of Friday through Monday:
  newDate = WorksheetFunction.WorkDay_Intl(#11/20/2011#, 10, "1000111", holidays)
  Debug.Print newDate

  ' The WorksheetFunction.NetworkDays and WorksheetFunction.NetworkDays_Intl
  ' functions work like the WorkDay and WorkDay_Intl functions,
  ' except that they return the number of work days between two dates.
  ' This example demonstrates the NetworkDays_Intl function. Find the
  ' number of work days between #11/1/2011# and #1/1/2012#, taking into
  ' account holiday, and treating Friday through Monday as weekend days:
  Dim workDays As Integer
  holidays = Array(#11/24/2011#, #12/25/2011#, #1/1/2012#)
  workDays = WorksheetFunction.NetworkDays_Intl(#11/1/2011#, #1/1/2012#, "1000111", holidays)
  Debug.Print "There are " & workDays & " workdays between 11/1 and 1/1"

  ' Try the WorksheetFunction.WeekNum function, which returns
  ' the week number that contains the specified date. Note that this function
  ' considers the week that contains Jan 1 to be the first week of the year.
  ' This differs from a standard European method of calculating the
  ' week number for a date, so the function returns dates that are
  ' incorrect for countries that use the differing standard.
  ' Indicate the first day of the week in the second parameter (1
  ' for Sunday, the default value; 2 for Monday):

  Dim weekNum As Integer
  weekNum = WorksheetFunction.weekNum(Date, 1)
  Debug.Print "The current week number is: " & weekNum
End Sub

Test the Solution

In this task, you step through the VBA code that demonstrates various date-related functions of the WorksheetFunction object.

To run the code

  1. Read through the code comments to get a better idea of what the code does.

  2. Now, still in the Visual Basic Editor, press F8 to step through the code line-by-line and read the information that is written to the Immediate window.

Next Steps