Working With Dates and Times

This is something I’ve been wanting to do for quite a while, that is, to create a page dedicated to working with Dates and Times.

I was doing a little more advanced Date/Time VBA programming and decided that now was as good a time as ever to start creating such a page. So here it is.

This article will evolve with time, so come back from time to time.

Table of Contents

- The Basics!
- Let’s Have Some Fun!

The Basics!

Let’s start with the basics and simply examine the native functions typically available to us for working with Dates and Times.

Get Current Date/Time

Function Name	Resulting Output
Date()	10/6/2024
Time()	3:15:08 PM
Now()	10/6/2024 3:15:08 PM

Build Date/Time Values

Function Name	Usage Example	Resulting Output
DateSerial()	DateSerial(2024, 10, 6)	10/6/2024
TimeSerial	TimeSerial(15, 15, 8)	3:15:08 PM
TimeSerial	TimeSerial(3, 15, 8)	3:15:08 AM
CDate()	CDate(“October 6, 2024”)	10/6/2024
CDate()	CDate(“2024-10-6”)	10/6/2024

Extract Date/Time Components

Function Name	Usage Example	Resulting Output	Note(s)
Year()	Year(#10/6/2024#)	2024
Month()	Month(#10/6/2024#)	10
MonthName()	MonthName(Month(#10/6/2024#))	October
MonthName()	MonthName(Month(#10/6/2024#), True)	Oct
Day()	Day(#10/6/2024#)	6
Weekday()	Weekday(#10/6/2024#)	1	(1=Sunday, this assume Sunday is the 1st day of the week)
Weekday()	Weekday(#10/6/2024#, vbMonday)	7	(7= Sunday, since we specified Monday as day 1)
WeekdayName()	WeekdayName(Weekday(#10/6/2024#))	Sunday
Hour()	Hour(#10/6/2024 3:15:08 PM#)	15
Minute()	Minute(#10/6/2024 3:15:08 PM#)	15
Second()	Second(#10/6/2024 3:15:08 PM#)
DateValue()	DateValue(#10/6/2024 3:15:08 PM#)	10/13/2024
TimeValue()	TimeValue(#10/6/2024 3:15:08 PM#)	3:15:08 PM

Calculations

Function Name	Usage Example	Resulting Output
DateAdd()	DateAdd(“d”, 7, #10/6/2024 3:15:08 PM#)	10/13/2024 3:15:08 PM
DateDiff()	DateDiff(“d”, #10/13/2024 3:15:08 PM#, Now())	5

Other(s)

Format() Function

Function Name	Usage Example	Resulting Output
Format()	Format(Now(), “yyyy-mm-dd”)	2024-10-06
	Format(Now(), “yyyy-mm-dd hh:nn:ss”)	2024-10-06 20:12:26
	Format(Now(), “yyyy-mm-dd h:mm:ss AM/PM”)	2024-10-06 8:12:26 PM
	Format(Now(), “mmm”)	Oct
	Format(Now(), “mmmm”)	October

Many more possibilities as you can build any desired output format you wish!

FormatDateTime() Function

Function Name	Usage Example	Resulting Output	Note(s)
FormatDateTime()	FormatDateTime(Date, vbLongDate)	Sunday, October 6, 2024	(based on your PC’s Regional Setting)
	FormatDateTime(Date, vbGeneralDate)	10/6/2024	(based on your PC’s Regional Setting)
	FormatDateTime(Now, vbGeneralDate)	10/6/2024 8:10:18 PM	(based on your PC’s Regional Setting)
	FormatDateTime(Now(), vbShortDate)	10/6/2024	(based on your PC’s Regional Setting)
	FormatDateTime(Now(), vbLongTime)	8:10:18 PM	(based on your PC’s Regional Setting)
	FormatDateTime(Now(), vbShortTime)	20:10	(24-hour format (hh:mm))

Let’s Have Some Fun!

Now that we know the basic functions available to us for working with Dates and Time values, let’s start using them to actually do common tasks.

Tomorrow

The function below is for calculating the next day’s date. If no date is passed to the function from which to perform the calculation, today is used.

'? GetTomorrow
'   10/8/2024 8:00:00 AM 'Run on 10/7/2024 8:00:00 AM
'? GetTomorrow(#7/15/2024 8:00:00 AM#)
'   7/16/2024 8:00:00 AM
Public Function GetTomorrow(Optional dtStartDate As Date) As Date
    If dtStartDate = "12:00:00 AM" Then dtStartDate = Now()
    GetTomorrow = DateAdd("d", 1, dtStartDate)
End Function

Yesterday

The function below is for calculating the previous day’s date. If no date is passed to the function from which to perform the calculation, today is used.

'? GetYesterday
'   10/6/2024 5:00:40 PM 'Run on 10/7/2024 5:00:40 PM
'
'? GetYesterday(#10/7/2024#)
'   10/6/2024
'
'? GetYesterday(#10/7/2024 8:00:00 AM#)
'   10/6/2024 8:00:00 AM
Public Function GetYesterday(Optional dtStartDate As Date) As Date
    If dtStartDate = "12:00:00 AM" Then dtStartDate = Now()
    GetYesterday = DateAdd("d", -1, dtStartDate)
End Function

Last Day of the Week

The function below is for calculating the last day in the week for the specified date. If no date is passed to the function from which to perform the calculation, today is used.

Function GetWeekEndDate(Optional dtStartDate As Date, _
                        Optional lFirstDayOfWeek As VbDayOfWeek = vbSunday) As Date
    If dtStartDate = "12:00:00 AM" Then dtStartDate = Date

    GetWeekEndDate = DateAdd("d", 7 - Weekday(dtStartDate, lFirstDayOfWeek), dtStartDate)
End Function

First Day of the Week

The function below is for calculating the first day in the week for the specified date. If no date is passed to the function from which to perform the calculation, today is used.

Function GetWeekStartDate(Optional dtStartDate As Date, _
                          Optional lFirstDayOfWeek As VbDayOfWeek = vbSunday) As Date
    If dtStartDate = "12:00:00 AM" Then dtStartDate = Date
    GetWeekStartDate = DateAdd("d", -(Weekday(dtStartDate, lFirstDayOfWeek) - 1), dtStartDate)
End Function

Get Last Week’s Date

The function below is for calculating the date one week in the past (last week’s date) for the specified date. If no date is passed to the function from which to perform the calculation, today is used.

Public Function GetLastWeekDate(Optional dtStartDate As Date) As Date
    If dtStartDate = "12:00:00 AM" Then dtStartDate = Date
    GetLastWeekDate = DateAdd("d", -7, dtStartDate)
End Function

Get Next Week’s Date

The function below is for calculating the date one week in the future (next week’s date) for the specified date. If no date is passed to the function from which to perform the calculation, today is used.

Public Function GetNextWeekDate(Optional dtStartDate As Date) As Date
    If dtStartDate = "12:00:00 AM" Then dtStartDate = Date
    GetNextWeekDate = DateAdd("d", 7, dtStartDate)
End Function

Get the 1st of the Month

The function below is for calculating the 1 of the month for the specified date. If no date is passed to the function from which to perform the calculation, today is used.

Public Function GetFirstOfMonth(Optional dtStartDate As Date) As Date
    If dtStartDate = "12:00:00 AM" Then dtStartDate = Date
    GetFirstOfMonth = DateSerial(year(dtStartDate), month(dtStartDate), 1)
End Function

and alternative version could be:

Public Function GetFirstOfMonth(Optional iYear As Integer, _
                                 Optional iMonth As Integer) As Date
    If iYear = 0 Then iYear = year(Date)
    If iMonth = 0 Then iMonth = month(Date)

    GetFirstOfMonth = DateSerial(iYear, iMonth, 1)
End Function

Get the Last of the Month

The function below is for calculating the last day of the month for the specified date. If no date is passed to the function from which to perform the calculation, today is used.

Public Function GetLastOfMonth(Optional dtStartDate As Date) As Date
    If dtStartDate = "12:00:00 AM" Then dtStartDate = Date
    GetLastOfMonth = DateSerial(year(dtStartDate), month(dtStartDate) + 1, 0)
End Function

and alternative version could be:

Public Function GetLastOfMonth(Optional iYear As Integer, _
                                Optional iMonth As Integer) As Date
    If iYear = 0 Then iYear = year(Date)
    If iMonth = 0 Then iMonth = month(Date)

    GetLastOfMonth = DateSerial(iYear, iMonth + 1, 0)
End Function

Determine if a Year is a Leap Year

Public Function IsLeapYear(iYear As Integer) As Boolean
    IsLeapYear = (iYear Mod 4 = 0 And iYear Mod 100 <> 0) Or (iYear Mod 400 = 0)
End Function

Get the 1st day of the Year

Public Function GetFirstDayOfYear(dtStartDate As Date) As Date
    GetFirstDayOfYear = DateSerial(year(dtStartDate), 1, 1)
End Function

Get the Last day of the year

Public Function GetLastDayOfYear(dtStartDate As Date) As Date
    GetLastDayOfYear = DateSerial(year(dtStartDate), 12, 31)
End Function

Get the 1st Day of a Quarter

Public Function GetFirstDayOfQuarter(dtStartDate As Date) As Date
    GetFirstDayOfQuarter = DateSerial(year(dtStartDate), 3 * ((month(dtStartDate) - 1) \ 3) + 1, 1)
End Function

Get the Last Day of a Quarter

Public Function GetLastDayOfQuarter(dtStartDate As Date) As Date
    GetLastDayOfQuarter = DateAdd("d", -1, DateSerial(year(dtStartDate), 3 * ((month(dtStartDate) - 1) \ 3) + 4, 1))
End Function

Get the Week Number

Public Function GetWeekNo(dtStartDate As Date) As Integer
    GetWeekNo = DatePart("ww", dtStartDate, vbSunday, vbFirstJan1)
End Function

Get the Nth Day in the Month

'? GetNthDayOfMonth(2024, 10,vbMonday, 1)
'   10/7/2024
'
'? GetNthDayOfMonth(2024, 10,vbMonday, 3)
'   10/21/2024
'
'? GetNthDayOfMonth(2024, 11, vbwednesday, 3)
'   11/20/2024
Function GetNthDayOfMonth(iTargetYear As Integer, _
                          iTargetMonth As Integer, _
                          iTargetDayOfWeek As VbDayOfWeek, _
                          iNthOccurrence As Integer) As Date
    Dim dtStartMonth          As Date
    Dim dtDate                As Date
    Dim iFirstOccurrenceDay   As Integer

    ' Validate inputs
    If iTargetMonth < 1 Or iTargetMonth > 12 Then
        Err.Raise 5, , "Invalid month. Must be between 1 and 12."
    End If

    If iNthOccurrence < 1 Or iNthOccurrence > 6 Then    'never be more than 5 occurences in a month
        Err.Raise 5, , "Invalid occurrence. Must be between 1 and 5."
    End If

    ' Get the first day of the specified month
    dtStartMonth = DateSerial(iTargetYear, iTargetMonth, 1)

    ' Calculate the first occurrence of the specified day in the month
    iFirstOccurrenceDay = iTargetDayOfWeek - Weekday(dtStartMonth, vbSunday) + 1
    If iFirstOccurrenceDay <= 0 Then
        iFirstOccurrenceDay = iFirstOccurrenceDay + 7
    End If

    ' Calculate the result date
    dtDate = DateSerial(iTargetYear, iTargetMonth, iFirstOccurrenceDay + (iNthOccurrence - 1) * 7)

    ' Check if the result is still in the specified month
    If month(dtDate) <> iTargetMonth Then
        Err.Raise 5, , "The specified occurrence does not exist in this month."
    End If

    GetNthDayOfMonth = dtDate
End Function

Convert a Time String Into Day(s) hh:mm:ss

'Convert a time value into a 'days hh:mm:ss' string
'? ConvertTimeToDays("96:75:00")
'   4 days 01:15:00
'
'? ConvertTimeToDays("48:00:00")
'   2 days 00:00:00
'
'? ConvertTimeToDays("18:59:00")
'   18:59:00
Function ConvertTimeToDHMS(sTime As String) As String
    Dim lTotalSeconds         As Long
    Dim lDays                 As Long
    Dim lHours                As Long
    Dim lMinutes              As Long
    Dim lSeconds              As Long
    Dim sOutput               As String
    Dim aTimeComponents       As Variant

    ' Split the input string into hours, minutes, and seconds
    aTimeComponents = Split(sTime, ":")

    ' Calculate total seconds
    lTotalSeconds = CLng(aTimeComponents(0)) * 3600 + _
                    CLng(aTimeComponents(1)) * 60 + _
                    CLng(aTimeComponents(2))

    ' Calculate days, hours, minutes, and seconds
    lDays = lTotalSeconds \ 86400
    lHours = (lTotalSeconds Mod 86400) \ 3600
    lMinutes = (lTotalSeconds Mod 3600) \ 60
    lSeconds = lTotalSeconds Mod 60

    ' Format the result string
    If lDays > 0 Then sOutput = lDays & " days "
    sOutput = sOutput & Format(lHours, "00") & ":" & _
              Format(lMinutes, "00") & ":" & _
              Format(lSeconds, "00")

    ConvertTimeToDHMS = sOutput
End Function

Convert a Time String into Seconds

Function ConvertTimeToS(sTime As String) As Long
    Dim lTotalSeconds         As Long
    Dim aTimeComponents       As Variant

    ' Split the input string into hours, minutes, and seconds
    aTimeComponents = Split(sTime, ":")

    ' Calculate total seconds
    lTotalSeconds = CLng(aTimeComponents(0)) * 3600 + _
                    CLng(aTimeComponents(1)) * 60 + _
                    CLng(aTimeComponents(2))

    ConvertTimeToS = lTotalSeconds
End Function

Convert Seconds into Day(s) hh:mm:ss

Function ConvertSecondsToDHMS(lTotalSeconds As Long) As String
    Dim lDays                 As Long
    Dim lHours                As Long
    Dim lMinutes              As Long
    Dim lSeconds              As Long
    Dim sOutput               As String

    lDays = lTotalSeconds \ 86400
    lHours = (lTotalSeconds Mod 86400) \ 3600
    lMinutes = (lTotalSeconds Mod 3600) \ 60
    lSeconds = lTotalSeconds Mod 60

    If lDays > 0 Then sOutput = lDays & " days "
    sOutput = sOutput & Format(lHours, "00") & ":" & _
              Format(lMinutes, "00") & ":" & _
              Format(lSeconds, "00")

    ConvertSecondsToDHMS = sOutput
End Function

Convert Seconds into hh:mm:ss

Function ConvertSecondsToHMS(lTotalSeconds As Long) As String
    Dim lHours                As Long
    Dim lMinutes              As Long
    Dim lSeconds              As Long

    lHours = lTotalSeconds \ 3600
    lMinutes = (lTotalSeconds Mod 3600) \ 60
    lSeconds = lTotalSeconds Mod 60

    ConvertSecondsToHMS = Format(lHours, "00") & ":" & _
                          Format(lMinutes, "00") & ":" & _
                          Format(lSeconds, "00")
End Function

Convert Seconds into mm:ss

Function ConvertSecondsToMS(lTotalSeconds As Long) As String
    Dim lMinutes              As Long
    Dim lSeconds              As Long

    lMinutes = lTotalSeconds \ 60
    lSeconds = lTotalSeconds Mod 60

    ConvertSecondsToMS = Format(lMinutes, "00") & ":" & Format(lSeconds, "00")
End Function

Convert Time into Seconds

'?TimeToSeconds(#1:01:30 PM#)
'   46890
'
'? TimeToSeconds(#13:01:30#)
'   46890
'
'?TimeToSeconds(#1:01:30#)
'   3690
Function TimeToSeconds(TimeValue As Date) As Long
    TimeToSeconds = Hour(TimeValue) * 3600 + Minute(TimeValue) * 60 + Second(TimeValue)
End Function

Get The Next Workday

Public Function GetNextWorkday(dtStartDate As Date, _
                               Optional vHolidays As Variant) As Date
    Dim vHoliday              As Variant
    Dim dtNextDay             As Date
    Dim bIsWorkday            As Boolean

    dtNextDay = dtStartDate

    Do While Not bIsWorkday
        dtNextDay = DateAdd("d", 1, dtNextDay)
        If Weekday(dtNextDay) = vbSaturday Or Weekday(dtNextDay) = vbSunday Then
            bIsWorkday = False
        Else
            bIsWorkday = True
            If Not IsMissing(vHolidays) Then
                For Each vHoliday In vHolidays
                    If DateValue(dtNextDay) = DateValue(vHoliday) Then
                        bIsWorkday = False
                        Exit For
                    End If
                Next vHoliday
            End If
        End If
    Loop

    GetNextWorkday = dtNextDay
End Function

Get The Previous Workday

Public Function GetPreviousWorkday(dtStartDate As Date, _
                                   Optional vHolidays As Variant) As Date
    Dim vHoliday              As Variant
    Dim dtPreviousDay         As Date
    Dim bIsWorkday            As Boolean

    dtPreviousDay = dtStartDate

    Do While Not bIsWorkday
        dtPreviousDay = DateAdd("d", -1, dtPreviousDay)
        If Weekday(dtPreviousDay) = vbSaturday Or Weekday(dtPreviousDay) = vbSunday Then
            bIsWorkday = False
        Else
            bIsWorkday = True
            If Not IsMissing(vHolidays) Then
                For Each vHoliday In vHolidays
                    If DateValue(dtPreviousDay) = DateValue(vHoliday) Then
                        bIsWorkday = False
                        Exit For
                    End If
                Next vHoliday
            End If
        End If
    Loop

    GetPreviousWorkday = dtPreviousDay
End Function

DEVelopers HUT