VBA Date Manipulation: Syntax & Functions Explained

The Date data type is one of the most commonly used data types in VBA that enables us to manipulate dates and times. In this blog post, we will explore the ‘Date’ data type in VBA and its syntax. We also introduce VBA functions that developers use to manipulate date and time in VBA.

 

How Date and Time are Stored and Displayed in Excel

 In Excel, dates and times are stored as integers formatted to look like dates. Dates in Excel are just the number of days since January 0, 1900, formatted to look like a date. Times are stored internally as numbers between 0 and 1. 

As you can see in the gif above, 0.25 is represented as  6:00 (quarter through the day). If you enter times as 6:00, Excel displays this time as 6:00:00 AM in the formula bar. AM is used for times in the night and morning and PM is used for times in the afternoon and evening.

When we enter a number in a cell, Excel assumes we want to use a general number format, which means no number format will be applied. If we type in a value, so it looks like a date or time, Excel automatically applies the default date and time format based on the regional date and time settings that are specified in Control Panel (It is important to choose a date format that Excel recognizes). It changes when we adjust the date and time settings in Control Panel.

Numbers can be displayed in several other date and time formats, some of which are not affected by Control Panel settings. Date and time formats that begin with an asterisk (*) respond to changes in regional date and time settings that are specified in Control Panel. Formats without an asterisk are not affected by Control Panel settings.

 

Date Data Type in VBA

In VBA, the Date data type is a 64-bit (8-byte) number that holds date and time values. It represents dates ranging from January 1 of the year 0001 through December 31 of the year 9999, and times from 12:00:00 AM (midnight) through 11:59:59.9999999 PM. Date can hold either the date, the time, or both as real numbers. The value to the left of the decimal represents a date, and the value to the right of the decimal represents a time. The default value of Date is 0:00:00 (midnight) on January 1, 0001.

 

Basic Date and Time Operations in Excel VBA

Date and Time Initialization

When it comes to initializing a variable in VBA, there are two ways to assign value to a Date variable.

• Date Literal: A date literal is any sequence of characters, surrounded by number signs (#) that has a valid format. Valid formats include the short date format recognized by your computer or the universal date format. Times are displayed according to the time format (either 12-hour or 24-hour). For example, #12/14/24# is the date literal of December 14, 2024, where the locale setting for your application is English-U.S. To maximize portability across national languages, use date literals.

Dim meinDate As Date Dim meinTime As Date Dim meinDateTime As Date meinDate = #1/1/2019# meinTime = #1:01:01 AM# meinDateTime = #1/1/2019 1:01:01 AM#

 

• Date Expressions: A date expression is any combination of numbers or strings, that can be interpreted as a date from January 1, 100 to December 31, 9999. This includes date literals, numbers that look like dates, strings that look like dates, and dates returned from functions.

Dim meinDate as Date  meinDate = Now()  meinDate = DateValue(“30 July 2016”)  meinDate = DateSerial(2016,7,30) meinDate = TimeValue(“19:22:56”)

 

Date Function

The Date() function returns the current system date. It does not require any arguments.

Dim meinDate as Date meinDate = Date()      MsgBox meinDate    ‘Returns 3\11\2024

IsDate Function

Returns a Boolean indicating whether or not the specified parameter is a date.

IsDate(“Dec 03, 2020”)    ‘Returns True IsDate(#01/14/20#)    ‘Returns True IsDate(#10/31/20 10:30 PM#)    ‘Returns True IsDate(“2.3.1988”)    ‘Returns False

Now Function

The Now() returns the current system date and time. It does not require any arguments.

Dim meinDate as Date meinDate = Now()      MsgBox meinDate    ‘Returns 3\11\2024 2:34:25 PM

Time Function

The Time() function returns the current system time. It does not require any arguments.

Dim meinDate as Date meinDate = Time() MsgBox meinDate    ‘Returns 2:37:43 PM

Year Function

Returns the year part of the specified date.

Dim meinDate As Date meinDate = #3/10/2022 12:02:23 PM# MsgBox Year(meinDate)    ‘Returns 2022

Month Function

Returns an integer between 1 and 12 representing the month part of the specified date.

Dim meinDate As Date meinDate = #3/10/2022 12:02:23 PM# MsgBox Month(meinDate)    ‘Returns 3

Day Function

Returns an integer between 1 and 31 representing the day part of the specific date.

Dim meinDate As Date meinDate = #3/10/2022 12:02:23 PM# MsgBox Day(meinDate)    ‘Returns 10

Hour Function

 Returns an integer between 0 and 23 that represents the hour part of the specified time.

Dim meinDate As Date meinDate = #3/10/2022 12:02:23 PM# MsgBox Hour(meinDate)    ‘Returns 12

Minute Function

Returns an integer between 0 and 59 that represents the minutes part of the specified time.

Dim meinDate As Date meinDate = #3/10/2022 12:02:23 PM# MsgBox Minute(meinDate)    ‘Returns 2

Second Function

Returns an integer between 0 and 59 that represents the seconds part of the specified time.

Dim meinDate As Date meinDate = #3/10/2022 12:02:23 PM# MsgBox Second(meinDate)    ‘Returns 23

MonthName Function

Returns a string that indicates the specified month. It takes two arguments.

• month: Required. The numeric designation of the month. For example, July is 7 and September is 9.
• abbreviate: Optional. Boolean. Default = Fales. Indicates if the month name is to be abbreviated. 

MonthName(12,True)    ‘Returns Dec MonthName(12,False)    ‘Returns December MonthName(1,True)    ‘Returns Jan MonthName(1,False)    ‘Returns January

WeekDay Function

Returns an integer between 1 and 7 that represents the day of the week for the specified date.

Dim meinDate As Date meinDate = #3/10/2022 12:02:23 PM# WeekDay(meinDate)    ‘Returns 5

WeekDayName Function

Returns the weekday name for the specified day number. It takes three arguments.

• weekday: Required. The number of the weekday.
• abbreviate − Optional. Boolean. Default: False. Indicates if the weekday name is to be abbreviated.
• firstdayofweek: Optional. Numeric. Specifies the first day of the week.
  • 0: Default. Use the National Language Support (NLS) API setting.
  • 1: Sunday
  • 2: Monday
  • 3: Tuesday
  • 4: Wednesday
  • 5: Thursday
  • 6: Friday
  • 7: Saturday

WeekdayName(3)    ‘Returns Tuesday WeekdayName(2,True)    ‘Returns Mon WeekdayName(1,False)    ‘Returns Sunday WeekdayName(2,True,0)    ‘Returns Mon WeekdayName(2,False,1)    ‘Returns Monday

DatePart Function

Returns a particular part(day, week, month, quarter, year) from the specified date. It takes four arguments.

• interval: Required. String. The part of time you want to return.
  • yyyy: Year
  • q: Quarter
  • m: Month
  • y: Day of Year
  • d: Day
  • w: Weekday
  • ww: Week
  • h: Hour
  • n: Minute
  • s: Second
• date: Required. Date value that you want to evaluate.
• firstdayofweek: Optional. Constant. Specifies the first day of the week. If not specified, Sunday is assumed.
  • 0: Default. Use the National Language Support (NLS) API setting.
  • 1: Sunday
  • 2: Monday
  • 3: Tuesday
  • 4: Wednesday
  • 5: Thursday
  • 6: Friday
  • 7: Saturday
• firstweekofyear: Optional. Constant. Specifies the first week of the year. If not specified, the first week is assumed to be the week in which January 1 occurs.
  • 0: Use the NLS API setting.
  • 1: Start with the week in which January 1 occurs (default).
  • 2: Start with the first week that has at least four days in the new year.
  • 3: Start with the first full week of the year.

Dim Quarter as Variant Dim DayOfYear as Variant Dim WeekOfYear as Variant Dim Month as Variant Dim meinDate As Date    MeinDate = “2019-03-15” Quarter    = DatePart(“q”, meinDate) MsgBox(Quarter)    ‘Returns 1 DayOfYear = DatePart(“y”, meinDate) MsgBox(DayOfYear)    ‘Returns 74 WeekOfYear = DatePart(“ww”, meinDate) MsgBox(WeekOfYear)    ‘Returns 11 Month = DatePart(“m”,meinDate) MsgBox(Month)    ‘Returns 3

 

Advanced Date and Time Manipulation Techniques

CDate Function

Converts any valid date expression to a Date data type, making it ready to be manipulated as date/time. If the expression passed to CDate is outside the range of the data type being converted to, an error occurs.

Dim meinDate As Date meinDate = CDate(“12.2.23 3-4-22”) MsgBox(meinDate)    ‘Returns a Date data type with value 3\4\2024 12:02:23 PM.

Compare Dates in VBA

As mentioned previously, dates and times are stored as real numbers in Excel and count the number of days since January 0, 1900. What we see depends on the number format we choose for a cell. For example, the value representing January 1, 2021, would be stored as 44197. This makes it easy to perform calculations and comparisons with dates in VBA. This means that comparing two or more Dates amounts to comparing two or more real numbers using <, >, <=, =>, = operators.

Dim meinDate1 As Date Dim meinDate2 As Date meinDate1 = “2019-03-15” meindate2 = “2019-03-17” MsgBox (meinDate1 > meindate2)    ‘Returns False

DateAdd Function

Adds or subtracts a specified time interval from a date. This function takes three arguments and returns a valid Date.

Syntax: DateAdd(Interval, Number, Date/Time)

• Interval: Required. String. Represents, which part of the Date/Time, you want the interval to be added.
  • yyyy: Year
  • q: Quarter
  • m: Month
  • y: Day of Year
  • d: Day
  • w: Weekday
  • ww: Week
  • h: Hour
  • n: Minute
  • s: Second
• Number: Required. Numeric. The number of intervals you want to add. It can be positive (to get dates in the future) or negative (to get dates in the past).
• Date: Required. Variant (Date) or literal. The date to which the interval is to be added.

Dim meinDate As Date meinDate = “2019-03-15” DateAdd(“yyyy”,1,meinDate)    ‘Returns 3/15/2020 DateAdd(“q”,1,meinDate)    ‘Returns 6/15/2019 DateAdd(“m”,1,meinDate)    ‘Returns 4/15/2019 DateAdd(“y”,1,meinDate)    ‘Returns 3/16/2019 DateAdd(“d”,1,meinDate)    ‘Returns 3/16/2019 DateAdd(“w”,1,meinDate)    ‘Returns  3/16/2019 DateAdd(“ww”,1,meinDate)    ‘Returns  3/22/2019 DateAdd(“h”,1,”01-Jan-2013 12:00:00″)    ‘Returns  1/1/2013 1:00:00 PM DateAdd(“n”,1,”01-Jan-2013 12:00:00″)    ‘Returns 1/1/2013 12:01:00 PM DateAdd(“s”,1,”01-Jan-2013 12:00:00″)    ‘Returns 1/1/2013 12:00:01 PM DateAdd(“yyyy”,-1,meinDate)    ‘Returns 3/15/2018 DateAdd(“q”,-1,meinDate)    ‘Returns 12/15/2018 DateAdd(“m”,-1,meinDate)    ‘Returns 2/15/2019 DateAdd(“y”,-1,meinDate)    ‘Returns 3/14/2019 DateAdd(“d”,-1,meinDate)    ‘Returns 3/14/2019 DateAdd(“w”,-1,meinDate)    ‘Returns 3/14/2019 DateAdd(“ww”,-1,meinDate)    ‘Returns 3/8/2019 DateAdd(“h”,-1,”01-Jan-2013 12:00:00″)    ‘Returns 1/1/2013 11:00:00 AM DateAdd(“n”,-1,”01-Jan-2013 12:00:00″)    ‘Returns 1/1/2013 11:59:00 AM DateAdd(“s”,-1,”01-Jan-2013 12:00:00″)    ‘Returns 1/1/2013 11:59:59 AM

DateDiff Function

Returns a Variant (Long) specifying the difference between two dates, in terms of the year, month, day, hours, seconds, etc. For example, we can use DateDiff to calculate the number of days between two dates, or the number of weeks between today and the end of the year. This function takes four arguments and returns a valid Date.

Syntax: DateDiff(interval, date1, date2, [ firstdayofweek, [ firstweekofyear ]] )

• Interval: Required. String. The interval of time you use to calculate the difference between date1 and date2.
  • yyyy: Year
  • q: Quarter
  • m: Month
  • y: Day of Year
  • d: Day
  • w: Weekday
  • ww: Week
  • h: Hour
  • n: Minute
  • s: Second
• date1,date2: Required; Variant (Date). Two dates at the beginning and end of calculations.
• Firstdayofweek: Optional. Constant. Specifies the first day of the week. If not specified, Sunday is assumed.
  • 0: Default. Use the National Language Support (NLS) API setting.
  • 1: Sunday
  • 2: Monday
  • 3: Tuesday
  • 4: Wednesday
  • 5: Thursday
  • 6: Friday
  • 7: Saturday
• firstweekofyear: Optional. Constant. Specifies the first week of the year. If not specified, the first week is assumed to be the week in which January 1 occurs.
  • 0: Use the NLS API setting.
  • 1: Start with the week in which January 1 occurs (default).
  • 2: Start with the first week that has at least four days in the new year.
  • 3: Start with the first full week of the year.

Dim meinDate1 As Date Dim meinDate2 As Date meinDate1 = “2019-03-15” meinDate2 = “2023-10-11” DateDiff(“yyyy”, meinDate1, meinDate2)    ‘Retruns 4 DateDiff(“q”, meinDate1, meinDate2)    ‘Retruns 19 DateDiff(“m”, meinDate1, meinDate2)    ‘Retruns 55 DateDiff(“y”, meinDate1, meinDate2)    ‘Retruns 1671 DateDiff(“d”, meinDate1, meinDate2)    ‘Retruns 1671 DateDiff(“w”, meinDate1, meinDate2)    ‘Retruns 238 DateDiff(“ww”, meinDate1, meinDate2)    ‘Retruns 239 DateDiff(“h”, meinDate1, meinDate2)    ‘Retruns 40104 DateDiff(“n”, meinDate1, meinDate2)    ‘Retruns 2406240 DateDiff(“s”, meinDate1, meinDate2)    ‘Retruns 144374400

DateSerial Function

Returns a Variant (Date) for a specified year, month, and day. The range of numbers for each DateSerial argument should be in the accepted range; that is, 1–31 for days and 1–12 for months. This function takes three arguments and returns a Date.

Syntax: DateSerial(year,month,day)

• year: Required. Integer. Number between 100 and 9999, inclusive, or a numeric expression.
• month: Required. Integer. Any numeric expression that evaluates to a value between 1 and 12, inclusive.
• day: Required. Integer.  Any numeric expression that evaluates to a value between 1 and 31, inclusive.

DateSerial(2023,5,10)    ‘Returns 5/10/2023 DateSerial(2004-1, 6, 30)    ‘Returns 6/30/2003 DateSerial(2004, 6-2, 14)    ‘Returns 4/14/2004

TimeSerial Function

Returns a Variant (Date) given an hour, minute, and second value. To specify a time, such as 11:59:59, the range of numbers for each TimeSerial argument should be in the normal range for the unit; that is, 0–23 for hours and 0–59 for minutes and seconds. This function takes three arguments and returns a valid Date that in fact contains time.

Syntax: TimeSerial( hour, minute, second )

hour: Required. Numerci. A value between 0 and 23, inclusive, that represents the hour value of the time.

minute: Required. Numeric. A value between 0 and 59, inclusive that represents the minute value of the time.

second: Required. Numeric. A value between 0 and 59, inclusive that represents the second value of the time.

TimeSerial(15, 6, 30)    ‘Returns 3:06:30 PM TimeSerial(20 – 8, 6, 30)    ’Returns12:06:30 PM TimeSerial(8, 6-2, 14)    ‘Returns 8:04:14 AM TimeSerial(7, -15, 50)    ’Returns 6:45:50 AM

DateValue Function

Returns the serial number or value of the date provided in string format ignoring the information of time. In simple words, it returns the serial number of the date. This function takes one argument and returns a Date.

Syntax: DateValue(date)

• date: Required. String. A date from January 1, 100, to December 31, 9999. It can also be any expression that represents a date, a time, or both a date and time, in that range.
  • Date must be a string comprising of only numbers separated by valid date separators (” “, “/”, “,”, “-“).
  • The order for month, day, and year is recognized according to the Short Date format that you specified for your system Control Panel.
  • If the year part of the date is omitted, the current year from your computer’s system date is used.
  • If the date argument contains valid time information, DateValue ignores it. However, if includes invalid time information (such as “34:74”), an error occurs.

DateValue(“12-3-2012”)    ‘Returns 12/3/2012 DateValue(“12-dec-2012”)    ‘Returns 12/12/2012 DateValue(“12-dec”)    ‘Returns 12/12/2024 DateValue(“dec 2023”)    ‘Returns 12/1/2023

TimeValue Function

Converts the given input string to a valid time i.e. the time serial number. The time’s serial number is a number between 0 and 1. For example, noon (halfway through the day) is represented as 0.5.

Syntax: TimeValue(time)

• time: Required. String. A date from January 1, 100, to December 31, 9999. It can also be any expression that represents a date, a time, or both a date and time, in that range. 

TimeValue(“22:30”)    ‘Returns 10:30 PM TimeValue(“5:25”)    ‘Returns 5:25:00 AM TimeValue(“2:34:48”)    ‘Returns 2:34:48 AM

 

Formatting Date and Time Output in Excel VBA

Format Function

Format function formats a string value according to the specified format.

Syntax: FORMAT(expression, format, firstdayofweek, firstweekofyear)

• expression: Required. Any valid expression.
• format: Optional. A valid named or custom format expression.
  • d: 1-31 (Day of month, with no leading zero)
  • dd: 01-31 (Day of month, with a leading zero)
  • w: 1-7 (Day of week, starting with Sunday = 1)
  • ww: 1-53 (Week of year, with no leading zero; Week 1 starts on Jan 1)
  • m: 1-12 (Month of year, with no leading zero, starting with January = 1)
  • mm: 01-12 (Month of year, with a leading zero, starting with January = 01)
  • mmm: Displays abbreviated month names (Hijri month names have no abbreviations)
  • mmmm: Displays full month names.
  • y: 1-366 (Day of year)
  • yy: 00-99 (Last two digits of year)
  • yyyy: 100-9999 (Three- or Four-digit year)
• Firstdayofweek: Optional. Constant. Specifies the first day of the week. If not specified, Sunday is assumed.
  • 0: Default. Use the National Language Support (NLS) API setting.
  • 1: Sunday
  • 2: Monday
  • 3: Tuesday
  • 4: Wednesday
  • 5: Thursday
  • 6: Friday
  • 7: Saturday
• firstweekofyear: Optional. Constant. Specifies the first week of the year. If not specified, the first week is assumed to be the week in which January 1 occurs.
  • 0: Use the NLS API setting.
  • 1: Start with the week in which January 1 occurs (default).
  • 2: Start with the first week that has at least four days in the new year.
  • 3: Start with the first full week of the year.

Dim meinTime, meinDate as Date meinTime = #22:04:53# meinDate = #January 3, 2009# Format(meinTime, “h:m:s”)    ‘ Returns “22:04:53”. Format(meinTime, “hh:mm:ss am/pm”)    ‘ Returns “22:04:53 pm”. Format(meinTime, “hh:mm:ss AM/PM”)    ‘ Returns “22:04:53 PM”. Format(meinDate, “dddd, mmm d yyyy”)    ‘ Returns “Saturday, Jan 3 2009”.

 

FormatDateTime

Takes a Date argument and formats it as a date or time (String). It was added in VB 6.0 and is slightly faster than FORMAT.

Syntax: FormatDateTime(Date, [ NamedFormat ])

• date: Required. Date (Variant). The Date type value to be formatted.
• NamedFormat: Optional. Integer. Specifies the format.
  • 0 = vbGeneralDate (default)
  • 1 = vbLongDate
  • 2 = vbShortDate
  • 3 = vbLongTime
  • 4 = vbShortTime

Dim meinTime, meinDate As Date

‘Arguments of FormatDateTime must be of Date type

meinTime = TimeValue(“04:30:00”) meinDate = DateValue(“1 Jan 2024”) FormatDateTime(meinTime, 0)    ‘Returns 4:30:00 AM FormatDateTime(meinTime, 3)    ‘Returns 4:30:00 AM FormatDateTime(meinTime, 4)    ‘Returns 4:30 FormatDateTime(meinDate, 0)    ‘Returns 1/1/2024 FormatDateTime(meieDate, 1)    ‘Returns Monday, January 1, 2024 FormatDateTime(meinDate, 2)    ‘Returns 1/1/2024

 

Conclusion

In this blog, we explained the importance of the Date data type in Excel VBA and explored a comprehensive range of Excel VBA functions for manipulating the Date data type. We started with the basic VBA functions and then got into advanced VBA tools for for handling Date. At the final part we introduced the FormatDateTime function with some examples to show how they are used in VBA code.