Manipulating Dates and Times

---

As with the number and text functions described earlier in this chapter, manipulating dates and times is more in the realm of the Excel formula than something that, strictly speaking, you need VBA to accomplish. However, it's important to be able to work with dates and times when you're doing advanced work in Excel, so it seems right to include these functions.

Time and Date Serial Numbers

If you've ever typed a date into a worksheet cell and then changed the cell's format to General or Number, you've seen the date change into a number. For example, if you type the date October 6, 2003 into a cell and then change the cell's format to General, the cell's value changes to 37900. The reason for the change in value is that Excel treats dates and times as numbers, not strings. In Excel, the default behavior for the program is to begin counting from January 1, 1900. So January 1, 1900, was day 1, January 2, 1900, was day 2, and so on. Representing a date as a number makes it easy to determine the number of days between two events. For example, if a company were founded on August 2, 1998, and first became profitable on January 9, 2004, you would know the number of days it took to attain profitability was 1986.

Important 

Dates prior to January 1, 1900, are treated as strings. You can't manipulate them using the date functions covered in the next section, but they are accepted as valid input.

Date and Time Functions

There are many instances when the date or time of an event would be important to you. If you use Excel to track orders placed with your business, you can enter the date and time of an order into your worksheet using the following VBA code:

ActiveCell.Value = Now

Yes, you could also enter the current date and time into a worksheet cell using the Excel formula =NOW, which returns the current date and time, but if you didn't take the time to replace the formula with its value by clicking the Copy button, clicking the Paste Options button (the arrow next to the button), and then selecting Values, Excel will recalculate the formula whenever you open the workbook and replace the time of the order with the current time. You can avoid that problem by creating a macro that assigns the value of Now to the active cell.

Now is not the only date or time function available to you in Excel VBA. Table 9-5 lists the functions available to you and describes them.

Table 9-5: Useful Functions for Finding All or Part of a Date or Time

Function	Description
Date	Returns the current date.
Time	Returns the current time.
Now	Returns the current date and time.
Timer	Returns the number of seconds since midnight.
DateValue	Given a character string, such as "August 2, 2004", returns a date (for example, 8/2/2004).
TimeValue	Given a character string, such as "19:30", returns a time (for example, 7:30:00 P.M.)
DateSerial (year, month, day)	Given a year, month, and day (for example, DateSerial(2004, 8, 2) returns the date (8/2/2004).
TimeSerial (hour, minute, second)	Given an hour, minute, and second, (for example, TimeSerial(19, 30, 24), returns the time (7:30:24 PM).
Hour, Minute, Second	Given a time, returns the hour, minute, or second component of that time.
Year, Month, Day	Given a date, returns the year, month, or day component of that date.
Weekday	Given a date, returns the weekday (Sunday = 1, Monday = 2, and so on) of that date.
MonthName	Given an integer, returns the month corresponding to that integer (for example, 8 returns August).

With the exception of the Weekday and MonthName functions, writing the result of any of the mentioned date and time functions to a worksheet cell means that Excel will format the cell with its default Date format.

There are a number of other date-related and time-related functions that you can use to perform calculations in your Excel VBA code. The two most useful functions are DateAdd and DateDiff. DateAdd, as the name implies, lets you add a time period to a date or time. Yes, you can add three days to a date with simple addition, as in the following procedure:

Public Sub AddDays()
 
    Dim datFirst, datSecond As Date
    datFirst = #1/9/2004#
    datSecond = datFirst + 3
    MsgBox ("The new date is: " & datSecond & ".")
 
End Sub 

But what if you want to add a month to datFirst? Depending on the month, you would need to add either 28, 30, or 31 days. The DateAdd procedure lets you add a variety of time increments to a date or time through the use of an ingenious syntax

DateAdd("interval", number, date)

in which interval is the code (in quotes) of the time unit, number is the number of times to increment the date or time by the interval, and date is the date or time to which you want to add the time. Table 9-6 lists the available units and their corresponding codes.

Table 9-6: Codes for Intervals Used in the DateAdd Function

Unit	Code
yyyy	Year
q	Quarter (three months)
m	Month
ww	Week
y	Day of year
d	Day
W	Weekday
h	Hour
n	Minute
s	Seconds

For example, if you wanted to use a message box to display the date nine weeks from today, you would use the following function:

MsgBox (DateAdd("ww", 9, DATE))

You can also use negative values within the DateAdd function to move backward in time. For example, the function to return the time eight hours ago (reckoning from the current time on your computer clock) would be

DateAdd("h", -8, NOW)

Note 

You can also use negative values in the DateSerial and TimeSerial functions, described earlier, but the DateAdd function gives you more flexibility by letting you use different time units.

The DateDiff function, as the name implies, lets you find the number of time units (that is, months, years, minutes, and so on) between two dates or times. The syntax is similar to that of the DateAdd function, but instead of using a base date and an increment to determine an end date, the DateDiff function uses two dates to determine the difference in interval between them.

DateDiff ("interval", date1, date2, firstdayofweek, firstweekofyear) 

The arguments of the DateDiff function are listed in Table 9-7.

Table 9-7: The DateDiff Function's Arguments

Argument	Description
Interval	An interval value listed in Table 9-6.
Date1	The first date to use in the calculation.
Date2	The second date to use in the calculation.
firstdayofweek	A constant that specifies the first day of the week. If not specified, Sunday is assumed. The available constants are vbUseSystem (use the system setting), vbSunday, vbMonday, vbTuesday, vbWednesday, vbThursday, vbFriday, and vbSaturday.
firstweekofyear	A constant that specifies the first week of the year. The available constants are vbUseSystem (use the system setting), vbFirstJan1 (the default, which uses the week that contains January 1), vbFirstFourDays (uses the first week with at least four days in the new year), vbFirstFullWeek (uses the first seven-day week in the new year).

For example, the following function returns the number of days between the current date and August 2, 2005 (remember that the date needs to be enclosed by pound signs):

DateDiff("d", NOW, #8/2/2005#)

When you subtract times, you don't need to use a special operator-the subtraction operator works, but you can run into problems if you cross midnight. For example, if you work from 9:00 P.M. to 6:00 A.M. you have worked nine hours, but subtracting .875 (the time serial of 9:00 P.M.) from .25 (the time serial of 6:00 A.M.) results in a negative number, which you can't have when working with time serials. The trick is to add a day to the smaller value to make the subtraction work. Adding 1 to the time serial for 6:00 A.M. results in the equation 1.25 - .875, which equals .375 (nine hours, or 9:00 A.M.). Here's how you implement this check in VBA:

'datTime1 is the start time, datTime2 is the finishing time
If datTime1 > datTime2 Then
datTime2 = datTime2 + 1
End If
datDifference = datTime2 - datTime 1 

Inside Out
Working with Times That Exceed 24 Hours

If you've ever tried to add two times together in an Excel worksheet, you've probably found that the program doesn't handle results of more than 24 hours gracefully. In fact, if you were to add 8:00 (8 hours), 7:00 (7 hours), and 10:00 (10 hours) together, the worksheet cell with the formula displays 1:00 (1 hour)! In other words, Excel disregards the first 24 hours and just shows the number of hours beyond the first 24. The same thing happens if you add two instances of 12:00 (12 hours) to the formula-even though the total number of hours is 49:00, the worksheet cell displays 1:00.

You can overcome this difficulty by formatting the cell in which you want to display the results with one of the custom data formats available in the Format Cell dialog box, which you open by clicking Format, Cells. Within the Format Cell dialog box, click the Custom category and scroll down until you see this time format: [h]:mm:ss.

The square brackets ([]) around the hour symbol mean that the normal limit of 24 hours no longer applies, so the cell will display time increments such as 25:00 correctly. You can do the same thing if you want to display more than 60 minutes ([mm]:ss) or more than 60 seconds ([ss]), although you'd have to create your own custom format by typing it into the Type box. Do remember that Excel won't let you create a format such as [h]:[mm]:ss- because there's no limit on the number of minutes, the number of hours would always be 0. Also, you should be aware that you can't put square brackets around a day indicator or a month indicator.

In this chapter, you've learned how to manipulate the data in your Excel workbooks. Whether you want to summarize the data using mathematical functions, concatenate the values from two or more cells to create detailed message boxes, or work with dates and times effectively, you can find those functions in Excel VBA. And if the functions aren't available in Excel VBA, there's a good chance you can call them from the main Excel program using the Application object's WorksheetFunction property.

---