Skip to: Site menu | Main content

Login

Name: 
Password:
Remember me?
Register

Dates and times

written by Helen Toomik - Last updated Mar 2013

What weekday was January 1st, 2001?
How can I make sure that users enter dates in the correct format?
How many Fridays are there in December 2004?
How can I save a file and name it based on today's date?

VB provides a variety of functions for manipulating dates. So many, in fact, that it can be difficult to keep track of them all - especially since their functionality often overlaps, and most date calculations can be done in several different ways. This tutorial aims to give an overview of the available functions, and show how they can be used to solve common problems, like the ones above.

International issues - different date formats - are a big part of working with dates. Does 1/3/04 mean March 1st or January 3rd? Several date functions give different results depending on the user's regional settings. I'll point out some common pitfalls, and try to offer some more solid approaches to writing internationally-aware code.

Because of this, the code samples I've included are likely to give different results on your system than they did on mine. The settings I used while writing this were: Swedish language, time format "HH:mm:ss", short date format "yyyy-MM-dd", long date format "d MMMM yyyy".

Working with times is very similar to working with dates, so if you learn how to work with dates, moving on to working with times should be an easy step. I'll mention the time equivalents of most of the date functions I cover, and point out any important differences I can think of.

Contents

  1. How VB handles dates and times
  2. What time is it?
  3. Creating dates
  4. Dates and strings
  5. Is it a date?
  6. From strings to dates
  7. From dates to strings
  8. Parts of dates
  9. Adding and subtracting
  10. Manual date math

How VB handles dates and times

In order to understand how date functions work, it's useful to have a rough idea of how VB handles dates and times internally.

VB has a Date data type. You can, for example, declare Dim MyBirthday As Date. In practice, Date values are stored as Doubles. The integer part holds the date, and the fractional part holds the time of the day. For example, 0:00 on September 19th, 2004, is equivalent to 38249. Midday is equivalent to 0.5, and 9:30 in the morning is 9.5/24 = 0.3958333. The starting point for VB's internal calendar, day 1, is 31 December 1899. Negative values are allowed as well: September 30th, 1452, is equivalent to -163354.

You can assign date values to variables declared As Double and vice versa. Any function that accepts doubles, will also accept dates. You can for example calculate the square root of a date - even though it's a rather meaningless calculation!

However, date variables are limited to a narrower range than doubles - the year has to be between 100 and 9999 (i.e. a 3- or 4-digit year). Outside that range you'll get an overflow error. If you need dates before or after this range, declare your variable As Double instead.

Given this limit, wouldn't it be easier to always declare dates As Double? To be honest, I can't see any disadvantages with that approach. The only advantage of using the Date data type is clarity. But as long as you name your date variables appropriately, by all means declare them As Double.

What time is it?

Functions you can use to the current date and/or time:

Now() returns current date and time
Date() returns current date
Time() returns current time

If you read the previous section, it won't surprise you that the following two lines of code give the same result:

 
Debug.Print Now() 
Debug.Print Date + Time 
 

Or, indeed, that these two will be equivalent:

 
Debug.Print Date 
Debug.Print Int(Now) 
 

If either of these does surprise you, you might want to go back and read the previous section again.

Be careful when using the Date() and Time() functions. There is also a Date statement, which sets the system date, and likewise a Time statement that sets the system time. Make sure you aren't accidentally calling the statement instead of the function, and changing the system time! If you want to be safe, use Now() instead.

 
Debug.Print Int(Now())              'a safer alternative To Date() 
Debug.Print Now() - Int(Now())      'a safer alternative To Time() 
 

Creating dates

Functions you can use to assign a value to a date variable (well, two functions and a format, to be precise):

DateSerial(year, month, day)
TimeSerial(hour, minute, second)
#date#

For example:

 
dtYearStart = DateSerial(intYear, 1, 1) 
dblLunchtime = TimeSerial(12, 30, 0)          'instead of 12.5/24 
dtChristmas = #12/25/2004# 
 

In the last case, you can type in the date in a different format, but it will change automatically into the American short date format. I typed in #25 dec 2004# and as soon as I hit Enter, it changed into what you see above.

DateSerial and TimeSerial can both handle inputs that are outside the "normal" range. If you enter a month number greater than 12, for example, DateSerial wraps that smoothly into the next year:

 
dtNextYear = DateSerial(2004, 13, 1)         'returns 1 January 2005 
 

Dates and strings

A date stored in memory is a double-precision number. But in order to be displayed, or saved to file, the date needs to be converted into a string. Likewise, if you are reading dates from a file or getting a date value from a user, the values often need to be converted from strings of characters back to a number.

This is a topic that causes endless confusion, and careless handling of date conversions often leads to errors, because of the various different date formats in use around the world. The main problem is the difference between American and European date formats - where American date formats are in the month-day-year order, and European dates are in the day-month-year order. Because of this, date strings can be ambiguous. There isn't always a definite and unequivocal date that corresponds to a given date string - the same string can mean different things in different places. Does 1/3/04 mean March 1st or January 3rd?

My rules of thumb for dealing with this are two:

When designing your user interface, don't assume a specific date format. For example, say your form has a textbox named txtDate where you want the user to enter a date. It is better to assume that the user will enter the date in the local date format, and interpret txtDate.Text using one of the date conversion functions, than to tell the user to use a predetermined format and then parse the string yourself. Likewise, use the local date format when displaying a date. This way you'll minimise the risk that the user misreads or ignores your instructions, or misinterprets the results.

When working with files, it's safest to write date variables to file as they are, and not convert them to strings at all. Use Write# or Put# to write data, and Input# and Get# to read them - not Print# and Line Input#. This way you'll get consistent results no matter what the local settings are. Print# may generate prettier files, but if your files will be read mainly by your own code and not by human eyes, prettiness is rather unimportant!

Note that Write# writes dates in the so-called universal date format, not in the American short date format. The universal date format is #yyyy-mm-dd hh:mm:ss#. If the time part is zero, it will be skipped and only the date gets written to the file; similarly if the date part is zero only the time is written to the file.

There are of course situations where these rules of thumb aren't practicable. Your output file may need to follow a certain format, for example because it will be used by some other application. In those cases, you'll have to take care of the formatting yourself, using functions that I'll go through below.

You may also find yourself working with data files generated by other applications using their own standards. If you need to import dates that are not in the universal date format, you may need to write your own date string parser. I'll get to that later.

Is it a date?

A function to check whether a string variable holds a valid date value:

IsDate()

In theory (= according to MSDN), IsDate "returns a Boolean value indicating whether an expression can be converted to a date." In reality, the function of IsDate is narrower than that - it tells you whether DateValue would be able to convert a particular string into a date. It returns False for integer and double-precision values that fall within the valid range for date variables, and could be converted into dates using CDate (but not using DateValue).

What do you think these will result in?

 
Debug.Print IsDate(#1/13/2003#) 
Debug.Print IsDate(37634) 
Debug.Print IsDate("13/01/2003") 
Debug.Print IsDate("01/13/2003") 
 

Answer: they will all return True except for the second one. You'd think that of #3 and #4, one would always fail - #3 to fail in the US, and #4 to fail in Europe. But if a date string looks invalid using the local date format, VB attempts to interpret it using a different date format, and therefore succeeds in reading both. (More on that in the next section, where I discuss DateValue in detail.) This means that you cannot use IsDate to check whether a date string conforms to the local date format.

From strings to dates

Functions that convert strings into dates and times:

CDate() from string or variant or number to date/time
DateValue() from string to date
TimeValue() from string to time

These are the functions you should use for converting a date or time string that the user has entered, into a numeric data type.

CDate is applicable more widely than DateValue and TimeValue - you can also use it to convert a Variant or any numeric data type to Date. Unlike CDate, DateValue only accepts string arguments.

For string arguments, CDate and DateValue give the same results. The only difference is that CDate returns a Date, and DateValue returns a Variant/Date. If you are assigning the results to a Date type variable, CDate is therefore marginally faster.

CDate and DateValue are internationally aware, and take into account the user's regional settings.

The functions do have a few quirks that you should be aware of, before using them.

Compared to DateValue, using TimeValue is relatively straightforward. As far as I'm aware, times are in the "hour:minute:second" order in all parts of the world.

When the format is "wrong"

If the string you are trying to convert does not conform to the local date format, you'll have to parse the string manually. Split the string into its component parts and put them together in the right order using DateSerial.

For example, say I need to read dates from a file, and the dates are stored as "yy/d/m" so that "04/1/3" stands for March 1st, 2004. My system would interpret that as January 3rd, 2004, so to read the date string correctly I have to parse it like this:

 
strDate = "04/1/3" 
dtDirect = DateValue(strDate) 
v = Split(strDate, "/") 
dtManual = DateSerial(v(0), v(2), v(1)) 
Debug.Print "Automatic conversion: "; dtDirect 
Debug.Print "Manual conversion:    "; dtManual 
 

If you find yourself doing this often, you may want to invest some time in developing a generic StringToDate function, to which you would pass a date string and a format string, and that returns a date.

From dates to strings

Functions that convert whole dates into strings:

CStr() returns a string in the local format
Format$() any kind of format can be specified

Functions that convert parts of dates into strings:

WeekDayName()
MonthName()

You can use CStr and Format$ to format dates for displaying on screen, or for writing to file.

CStr converts a number into a string, using the user's regional settings. For date variables, CStr uses the local short date format. If your date variable is not declared as Date but as Double, CStr will be less useful as it will return a string like "38251.7532523148".

Format$ is much more flexible, as it allows you to specify exactly how you want the date to be formatted. It allows you to either specify a format in detail, or to refer to the user's regional settings.

The predefined date/time formats available are "General Date", "Long Date", "Short Date", "Long Time" and "Short Time".

The list of codes for creating your own, custom formats is so long that I won't repeat them all here... check MSDN or your VBA help files for all the details, or check MSDN online . Here are few examples of format expressions, and the results I get with my regional settings:

 
Dim D As Date 
D = #9/21/2004 6:30:00 PM# 
Debug.Print Format$(D, "Short Time")        '18:30 
Debug.Print Format$(D, "Short Date")        '2004-09-21 
Debug.Print Format$(D, "m/d/yy")            '9-21-04 
Debug.Print Format$(D, "m""/""d""/""yy")    '9/21/04 
Debug.Print Format$(D, "dddd dd mmmm yyyy") 'tisdag 21 september 2004 
Debug.Print Format$(D, "dddd h:n AM/PM")    'tisdag 6:30 PM 
Debug.Print Format$(D, "dd mmm"" is day ""y"" of the year.") 
'21 sep Is day 265 of the year. 
 

If you look carefully, you'll see a couple of interesting results here...

Firstly, the / character is not interpreted literally, but as a placeholder for the local date separator. Notice how the 3rd Format$ function outputs a date with dashes between the parts, and not slashes. To override this and to force VB to take the slashes literally, you need to enclose them in double double quotes, like I've done in the 4th example.

This leads to a little useful trick. The following line will simply return the user's default date separator:

 
Debug.Print Format$(1, "/") 
 

Secondly, you can include all kinds of other literal text in the format expression, as long as you put enough quotes around it. The last line of code above is an example of that.

Thirdly, the Format$ function can perform some basic calculations on the date. It can tell you the day of the year as a number, or the number of the week, or the quarter. Again the last line of code above has an example. The DatePart function can do similar tricks, but returns the results as a number, which is more useful if you actually need the result for a calculation. But if you only need to show the result, there's no need to calculate and then build a string from the results of your calculations - Format$ can do it all in one go.

WeekdayName and MonthName are straightforward and do exactly what they promise. WeekdayName takes a day number (1 to 7) as its main argument, and MonthName likewise needs a month number (1 to 12). These two functions could be useful, for example, for populating a combo box with month names, which can be more convenient for the user than typing in a number.

Side issue: dates in file and folder names

The Format$ function is what you would use to solve one of the sample questions at the beginning of this tutorial - "How can I save a file and name it based on today's date?". One possible answer is this:

 
Dim strFileName As String 
strFileName = "myfile " & Format$(Date, "yyyy-mm-dd") & ".txt" 
 

Note that I'm not using the local date separator here. The reason is simple - the default date separator is often a slash, and slashes are not allowed in file names.

Note also that I've put the date parts in a particular order, starting with the year. File names formatted like this will sort nicely, and your files will be ordered by date:

Myfile 2002-12-31.txt
Myfile 2003-01-01.txt
Myfile 2003-01-02.txt
...

Whereas if I had named them using, for example, a "dd mmm yy" date format, listing the files by their names would result in a jumbled mess:

Myfile 01 Jan 03.txt
Myfile 02 Jan 03.txt
Myfile 31 Dec 02.txt
...

The same approach makes sense when naming folders. If you name them "January", "February" etc, they'll appear in a random order. But name them "01 - January", "02 - February" etc, and they'll sort very nicely.

Parts of dates

Functions that extract parts of dates and times:

Year(), Month(), Day(), Hour(), Minute(), Second()
WeekDay()
DatePart()

The first six of these functions do exactly what it says on the tin... return that part of the date, as an integer.

Note that you can pass these functions either a date variable, or a date string. This means that you can, for example, use Month() to convert month names into month numbers:

 
Dim s As String 
s = "december" 
Debug.Print Month("1 " & s & " 2004") 
'Prints 12 
 

Note that the string-to-date conversion that happens here follows the same rules as DateValue, and this trick only works if the month name is in the local language.

Weekday returns the number of the day of the week. Since a week starts on Sunday in the US but on Monday in Europe, you can specify which day you want to use as first day of the week. The default is vbSunday - not vbUseSystem, as you might reasonably assume! The Weekday function is very useful for all sorts of date math, and I'll be using it in several examples later on.

DatePart is like a combination of all the above, plus some more. In addition to year, month, day, hour, minute, second, and weekday, the DatePart function can also give you the number of the quarter or the week, and the day of the year. The first argument of DatePart is a string that specifies which part of the date (which interval) you're looking for - "w" for weekday, "d" for day, etc. For example:

 
Debug.Print "Today is day " & DatePart("w", Date, vbUseSystem) & _ 
" of week " & DatePart("ww", Date, , vbUseSystem) 
'Prints "Today is day 2 of week 38" For me on September 21, 2004 
 

Similar to the Weekday function, you need to specify the first day of the week for DatePart. If you want to calculate the week number, you also have tell it which week is the first week of the year. The VB default is that week 1 is the week of January 1st, but again, the rules vary between countries.

If you simply need to get the year, I would personally use the Year function - the resulting code will be easier to read, and you won't run the risk of typing in one "y" for the interval argument, when you really mean "yyyy". But for the more complex calculations like week number, DatePart is the right way to go.

Adding and subtracting

Functions to add and subtract dates:

DateAdd() Adds a specified interval to a date
DateDiff() Returns the difference between two dates

DateAdd adds a specified time interval to a date - for example, you can add two months to a date, or subtract a quarter. DateDiff calculates the difference between two dates, measured in intervals that you specify - for example in days, weeks, etc.

These two functions are similar to DatePart, in that they can work with any part of the date. Like DatePart, you specify the interval with a string like "m" for months and "q" for quarters. If you want to use intervals like "w", you also have to specify the starting day of the week, and which week is the first week of the year.

DateAdd adjusts automatically for shorter and longer months etc. For example, adding one month to January 31st will return the last day of February, and is not the same thing as adding 30 days to January 31st (unless it happens to be a leap year). Example:

 
Debug.Print DateAdd("m", 1, #1/31/2004#)        'prints 2004-02-29 
Debug.Print DateAdd("m", 1, #2/29/2004#)        'prints 2004-03-29 
 

DateDiff also takes the calendar into account. For example, the difference between 31 December 2001 and 1 January 2002 measured in years is 1, even though there is only one day between them, since the two dates fall in different calendar years.

Quarters and months also work with calendar quarters and calendar months. The difference between 31 March and 1 April is 1 month; the difference between 1 March and 31 March is 0 months.

If the interval is weeks ("ww"), VB counts calendar weeks. For example, the difference between a Sunday and the Monday just after it is 0 weeks if you specify vbSunday as first day of the week, but 1 week if first day of the week is vbMonday.

If the interval is weekdays ("w"), VB counts 7-day weeks. In this case it is 1 week from Sunday to Sunday, but 0 weeks from Sunday to Monday, no matter what the first day of the week is.

Manual date math

Since dates are just double-precision numbers, you can also use plain arithmetic to work with dates. In the simplest case, you may just want to add and deduct days. There's no need for DateAdd for such simple problems. For example, these two lines of code give the same result - but the first one is, in my opinion, much easier to read and understand:

 
Debug.Print Date + 1 
Debug.Print DateAdd("d", 1, Date) 
 

You can combine this with the functions I've gone through above to solve all kinds of date problems. Here are some examples of common types of date problems, with step-by-step explanations of the reasoning behind the code. All of them are relatively basic, but once you have understood how they work, you can easily customise them to make them more flexible etc.

Last day of current month

The last day of this month is, of course, the day before the start of the next month.

 
Debug.Print DateSerial(Year(Date), Month(Date) + 1, 1) - 1 
 

Use DateSerial to get the first day of the next month: Year(Date) gives the current year; Month(Date) + 1 gives the next month, and 1 gives the first day of the month. Subtract 1 to get to the previous day, i.e. the last day of this month.

DateSerial is perfectly capable of handling month numbers outside the 1 to 12 range, so we don't need to worry about year-end either. In fact, DateSerial is so flexible that the code can be made even shorter:

 
Debug.Print DateSerial(Year(Date), Month(Date) + 1, 0) 
 

In VB's reckoning, the 0th day of a month is the day before the 1st day of the month.

First day of current quarter

 
Dim Q As Integer 
Q = DatePart("q", Date) 
Debug.Print DateSerial(Year(Date), Q * 3 - 2, 1) 
 

Q is the number of the current quarter. Each quarter is three months, so Q*3 will give us the number of the last month of this quarter. Subtract 2, and we have the first month of this quarter. Finally, use DateSerial to build a date based on the current year, the month number we just calculated, and day 1.

First Friday of a month

 
Function FirstFriday(intYear As Integer, intMonth As Integer) As Date 
    Dim FirstDayOfMonth As Date 
    Dim DaysSinceFriday As Integer 
    FirstDayOfMonth = DateSerial(intYear, intMonth, 1) 
    DaysSinceFriday = Weekday(FirstDayOfMonth, vbSaturday) 
    FirstFriday = FirstDayOfMonth + (7 - DaysSinceFriday) 
End Function 
 

Start by finding the first day of the month, using DateSerial. Next, look at how many days have passed since the most recent Friday. Use the Weekday function for that, and specify Saturday as the first day of the week, so that we get 1 for a Saturday, 2 for a Sunday etc. Now if that day is a Saturday, and 1 day has passed since Friday, there are 6 days (7 - 1) to go until next Friday. Likewise if the first day of the month happens to be a Friday, there are 0 = 7 - 7 days to go until the nearest Friday.

How many Fridays between 2 dates

This example builds on the previous one.

 
Function CountFridays(FirstDay As Date, LastDay As Date) As Integer 
    Dim FirstFriday As Date 
    Dim DaysSinceFriday As Integer 
    Dim DaysAfterFirstFriday As Integer 
     
    If LastDay < FirstDay Then 
        CountFridays = 0 
    Else 
        DaysSinceFriday = Weekday(FirstDay, vbSaturday) 
        FirstFriday = FirstDay + (7 - DaysSinceFriday) 
        DaysAfterFirstFriday = LastDay - FirstFriday 
        If DaysAfterFirstFriday < 0 Then 
            CountFridays = 0 
        Else 
            CountFridays = DaysAfterFirstFriday  7 + 1 
        End If 
    End If 
End Function 
 

The function starts by checking that the period is at least 1 day long, and returns zero if not.

Otherwise, find the first Friday after the start of the period, using the same approach as in the previous example. Then get the number of days from that Friday to the last day. If that is less than 0, then the first Friday occurs after the end of the period, so there are no Fridays in the period.

If that Friday falls within the required period, calculate how many 7-day blocks you could fit into the remaining period, by dividing the number of remaining days by 7. (The "\" operator is for integer division. 6\7 returns 0 while 7\7 returns 1.)