>

    VBA Procedures For Dates And Times 

 

This page describes a number of procedures and functions for working with dates and times from VBA code in Microsoft Excel.  

 

        

First Monday Of The Year (YearStart)

The following function was provided by John Green, an Excel MVP from Australia.   It returns the first Monday of the specified year.  This function is used by other functions on this page.

Public Function YearStart(WhichYear As Integer) As Date

Dim WeekDay As Integer
Dim NewYear As Date

NewYear = DateSerial(WhichYear, 1, 1)
WeekDay = (NewYear - 2) Mod 7 'Generate weekday index where Monday = 0

If WeekDay < 4 Then
    YearStart = NewYear - WeekDay
Else
    YearStart = NewYear - WeekDay + 7
End If

End Function


Monday Of A Given Week Number (WeekStart)

This function returns the date of the Monday in a given week number, e.g., the Monday of the 15th week of 1998.  It requires the YearStart function, given above.

Public Function WeekStart(WhichWeek As Integer, WhichYear As _
                    Integer) As Date

WeekStart = YearStart(WhichYear) + ((WhichWeek - 1) * 7)

End Function

IsLeapYear

This function returns TRUE if the specified year is a leap year, or FALSE if it is not.

Public Function IsLeapYear(Y As Integer)
    IsLeapYear = Month(DateSerial(Y, 2, 29)) = 2
End Function

Military To Time

This function converts military time, supplied as an integer, to a standard serial time number. For example, it converts 1130 to 11:30, and 1650 to 16:50.

Public Function MilitaryToTime(T1 As Integer)
'
' Input T1: 24-hour time as integer,e.g., 1130=11:30, 1650=16:50
' Output, time as serial time e.g, 0.5 for noon.
'
Dim TT1 As Double

TT1 = Int(T1 / 100) + (((T1 / 100) - Int(T1 / 100)) / 0.6) '23.50
TT1 = TT1 / 24
MilitaryToTime = TT1

End Function


See the Date Time Entry page for some code to allow you to enter dates and times in this format, and have the converted automatically on entry to real Excel dates and times.


Nth Day Of Week

This function will return will return the date of Nth day-of-week for a given month and year. For example, it will return 26-March-98 for the 4th Thursday of March, 1998.   Days-of-week range from 1 to 7, with Sunday = 1 and Saturday = 7.

Public Function NthDayOfWeek(Y As Integer, M As Integer, _ 
    N As Integer, DOW As Integer) As Date

    NthDayOfWeek = DateSerial(Y, M, (8 - WeekDay(DateSerial(Y, M, 1), _
     (DOW + 1) Mod 8)) + ((N - 1) * 7))

End Function


Week Number (ISOWeekNum)

The following function was provided by John Green, an Excel MVP from Australia.   It returns the first week number of the specified year.  It requires the YearStart function, given earlier.

Public Function ISOWeekNum(AnyDate As Date, Optional WhichFormat As Variant) As Integer
' WhichFormat: missing or <> 2 then returns week number,
'                                = 2 then YYWW
'
Dim ThisYear As Integer
Dim PreviousYearStart As Date
Dim ThisYearStart As Date
Dim NextYearStart As Date
Dim YearNum As Integer

ThisYear = Year(AnyDate)
ThisYearStart = YearStart(ThisYear)
PreviousYearStart = YearStart(ThisYear - 1)
NextYearStart = YearStart(ThisYear + 1)
Select Case AnyDate
    Case Is >= NextYearStart
        ISOWeekNum = (AnyDate - NextYearStart) \ 7 + 1
        YearNum = Year(AnyDate) + 1
    Case Is < ThisYearStart
        ISOWeekNum = (AnyDate - PreviousYearStart) \ 7 + 1
        YearNum = Year(AnyDate) - 1
    Case Else
        ISOWeekNum = (AnyDate - ThisYearStart) \ 7 + 1
        YearNum = Year(AnyDate)
End Select

If IsMissing(WhichFormat) Then Exit Function
If WhichFormat = 2 Then
    ISOWeekNum = CInt(Format(Right(YearNum, 2), "00") & _
    Format(ISOWeekNum, "00"))
End If

End Function


Calculating Age In VBA
 

The following function will compute a person's age, returning a string such as "45 years 10 months 18 days".

Function Age(Date1 As Date, Date2 As Date) As String
    Dim Y As Integer
    Dim M As Integer
    Dim D As Integer
    Dim Temp1 As Date
    Temp1 = DateSerial(Year(Date2), Month(Date1), Day(Date1))
    Y = Year(Date2) - Year(Date1) + (Temp1 > Date2)
    M = Month(Date2) - Month(Date1) - (12 * (Temp1 > Date2))
    D = Day(Date2) - Day(Date1)
    If D < 0 Then
        M = M - 1
        D = Day(DateSerial(Year(Date2), Month(Date2) + 1, 0)) + D + 1
    End If
    Age = Y & " years " & M & " months " & D & " days"
End Function
 

 

Dates In The Find Method

Using the .Find  method to search for dates can be a bit tricky.  Regardless of how the date is formatted to display (as long as it is a date format of some sort), you must search for it in its "standard" format, e.g., "7/18/1998" rather than "7/18/98" or "July 18, 1998".   For example,

Set FoundCell = Range("A1:A100").Find (what:="7/18/1998")

Alternatively, you can use the DateValue function to convert any date format into an Excel serial date, and search the formulas of the range (even though they are not formulas in the conventional sense) to find your date.

Set FoundCell = Range("A1:A100").Find _
   (what:=DateValue("July 18, 1998") ,lookin:=xlFormulas)

 

 

 

Other Date And Time Related Procedures are described on the following pages.

        

Adding Months And Years

The DATEDIF Function

Date Intervals

Dates And Times

Date And Time Entry

Date And Time Arithmetic

Distributing Dates Over Intervals

Holidays

Julian Dates

Latitude And Longitude 

Overtime Hours And Timesheets


Time Zones In VBA

VBA Procedures For Dates And Times

Week Numbers

Worksheet Functions For Dates And Times

 

 

See the Dates And Times Topic Index For Information