DateSerial( ) and TimeSerial( )

The DateSerial() and TimeSerial() functions can be used to create a date or time by passing the parts of the value as separate parameters to the function. The DateSerial() parameters are the year, month and day. The DateTime() parameters are the hour, minute, and seconds.

In the simplest form, these functions create a date or time using three parameters. But these functions are also very powerful for adding and subtracting values to a Date and Time. They are different from the DateAdd() function in that they perform the calculations using a cumulative process. They start by calculating a partial date (or time) value and build upon it and modify it each step of the way. It starts by calculating the year, then it calculates the month and finally the day. This is easiest to see by looking at a simple example first and a more complex example second. All of the examples use the following statements to declare and initialize the variable MyDate.

This code snippet shows the variable declaration that is used for the remaining examples.

‘Declare the variable for use in the examples
Dim MyDate as Date
MyDate = CDate("2/4/2002")

This example gets the current year and month from the current date and passes them to the DateSerial() function. It passes the value 1 as the day parameter to force it to return the first day of the current month.

Formula = DateSerial(Year(MyDate), Month(MyDate), 1) ‘Returns 2/1/2002

The next function calculates the last day of the prior month by using each parameter to create the next part of the date in sequence and modifying the result according to the arithmetic.

Formula = DateSerial(Year(MyDate), Month(MyDate), 1 - 1) ‘Returns 1/31/2002

How it calculates the result is best shown using the steps listed here.

• Calculate the year. This returns a date with the year of 2002.
• Calculate the month. This returns a date of 02/2002.
• Calculate the day. The first part of the parameter is 1 and this returns a date of 02/01/2002.
• The subtract operator tells it to subtract one day from the date as it has been calculated to this point. Thus, it subtracts one day from 02/01/2002 to give a date of 1/31/2002.

The next example is the most complex, but uses the same rules as the last example. It calculates the last day of the current month.

Formula = DateSerial(Year(MyDate), Month(MyDate) + 1, 1 - 1) ‘Returns 2/28/2002

Calculate the year. This returns a date with the year of 2002.

Calculate the month. This returns a date of 02/2002

The addition operator tells it to add one month. This returns a date of 03/2002.

Calculate the day. The first part of the parameter is 1 and this returns a date of 03/01/2002.

The subtract operator tells it to subtract one day from the date as it has been calculated to this point. Thus, it subtracts one day from 03/01/2002 to give a date of 2/28/2002.

You can see from the three previous examples that using a cumulative approach to calculating the date is very powerful. It’s almost like using a single function to write a simplified macro.