5.5 Working with Dates and Times
5.5 Working with Dates and Times
Working with dates and times has always been a task that is more difficult than it appears to be at first glance. With its host of date and time functions, Microsoft has done little to improve the situation.
Excel and the Year 2000
Let me state at once that Excel has no difficulty with representing the year 2000 correctly. A possible source of problems, however, is the automatic addition of the century when only the last two digits of a year are given. Usually, a window of time is applied; that is, two-digit years are automatically attributed to the period from 1930 to 2029. It is readily admitted that this interval is not a constant of nature, and it could change in the future (with a different operating system or version of Office). With Windows 98 and Windows 2000, in fact, this interval can be set by the user (Control Panel/Regional Settings/Date), which can open the floodgates to untold trouble. You would do well not to rely on these settings.
In general, in your program code you should represent all years with four digits, without exception. (Fortunately, beginning with Excel 2000, dates input in the form #12/31/1999# are automatically represented with four digits. In Excel 97 this was impossible for some unexplained reason.) If you permit input into a form in two-digit format (it's less work ), then you should immediately convert this input to four-digit representation so that the user receives, while still interacting with the form, a reply showing how the input date has been interpreted internally.
| Tip | Dates in worksheets are usually represented with only two digits. The reason for this is that for formatting it frequently happens that the "short date format" predefined by the system setting is used, and this contains only two digits. You can change this setting globally in the local settings by using yyyy instead of simply yy (Figure 5-2). The new setting then holds in all programs that use this format (and also, by the way, for the VBA functions Format and FormatDateTime ). |
Figure 5-2: Four-digit years as the global default setting
Dates and Times in Program Code
In VBA procedures dates and times are enclosed between two # signs, for example, #12/31/1999# or #17:30# or even the combined #12/31/1999 17:30# . Sad to say, Excel turns #17:30# into #5:30:00 PM# , whether you want it to or not, but at least it accepts #17:30# as valid input. However, #31.12.1997# is not permitted, and it leads to an error message. Dates must be input in American format (month/day/year).
American notation for dates and times is required only in VBA code. In conversion from or to character strings, VBA conforms to the regional setting under Windows. Thus it is only you as the programmer who is confronted with the requirement of American format, not the user of your VBA program. In the German regional setting, for example, MsgBox "Datum:" & #12/31/1999# displays the character string "Datum: 31.12.1999" . Similarly, the character string "31.12.1999" is properly processed by most functions.
If you wish to use an international format in program code as well, then you can declare dates and times as character strings in the Cdate function, say, CDate("31.12.1999") instead of #12/31/1999# or CDate("17:30") instead of #5:30:00 PM# . These time inputs will be correctly converted according to the Windows regional setting. However, this form of time declaration has two drawbacks: First, the code runs ( minimally ) slower, and second, the code is not portable (since the results of CDate depend on the regional settings of the computer on which the code is executed).
A further alternative for declaration of dates and times is offered by the functions DateSerial(year, month, day) and TimeSerial( hour , minute, second) . This does not look like particularly compact code, but at least it is unambiguous and independent of the regional setting.
Internal Representation (VBA) of Dates and Times
Date and time are represented internally in VBA by floating point numbers . The number 1 corresponds to the date 31 December 1899, the number 2 to 1 January 1900, etc. The time is stored in the digits to the right of the decimal point. Thus 2.25 corresponds to 1 January 1900, 6:00 A.M., while 34335.75 corresponds to 1 January 1994 at 6:00 P.M.
| Note | In worksheets, floating point numbers are represented as a date or time only if the cell in question has been set to contain a date or time format. If a date or time is input at the keyboard, then Excel automatically chooses a suitable format. On the other hand, if you carry out calculations with dates and times, you yourself are responsible for the correct formatting of the result cell . |
As far as Excel is concerned , dates and times are just ordinary numbers that can be employed in calculations without further processing. Therefore, you can effortlessly calculate the difference between two dates or two times or add a given number of days to a particular date.
In Excel tables the valid range of dates is from 1 January 1900 to 31 December 2078. In VBA procedures in which the variable type Date is used, dates are allowed between 1 January 100 and 31 December 9999. In this case, dates before 1 January 1900 are represented as negative numbers.
To achieve compatibility with the Macintosh version of Excel there is the option, under TOOLSOPTIONSCALCULATION, to use the 1904 date system, in which the number 0 corresponds to 1 January 1904. This change is valid in both worksheets and VBA procedures.
Errors
Dates between 1/1/1900 and 2/28/1900 are incorrectly converted between VBA and Excel tables. If you execute the instruction Worksheets(1).[A1]=#1/1/1900# , you will find in cell A1 the date 1/2/1900! If you directly type 1/1/1900 into cell A1 and then execute the VBA instruction ?Worksheets(1).[A1] , then the result is 12/31/1899.
The cause of the error is easy to establish. In Excel worksheets the leap day 2/29/1900 is allowed, but not in VBA. (VBA is correct!) In the year 2000, which is the critical one, both date functions work correctly and accept 2/29/2000 as a valid date.
The result is that within Excel tables dates before 3/1/1900 cause trouble. Since the entire range of validity for dates begins only with 1/1/1900, the problem is restricted to a period of two months. The VBA date function seems to function entirely correctly (at least all attempts to find an error have failed).
For the interested reader, here is the background: According to the Gregorian calendar, which is the calendar used in most of the world today, every year that is divisible by 4 is, in general, a leap year, such as the years 1988, 1992, and 1996. However, if a year is divisible by 100, then it is not a leap year, unless it is also divisible by 400, in which case it is. Therefore, 1700, 1800, and 1900 are not leap years, but 1600 and 2000 are.
Overview of the Remainder of the Section
The numerous functions for dates and times constitute a veritable Tower of Babel. There are functions that can be used only in worksheets, others that can be used only in VBA procedures, and still others that are doubly defined for both VBA and worksheets, those that can be used in VBA code only if prefixed by Application.WorksheetFunction , and so on.
So that you do not lose your overview entirely, the following description goes subsection by subsection. First are described the functions that can be employed in VBA code. Then worksheet functions are described, with like-named functions referred to in the first section. We then go on to demonstrate application techniques in both worksheets and VBA code. The next subsection goes especially into the problem of holidays, which vary from country to country and thus cannot be provided independently by Excel. The last subsection provides the usual denouement: a syntax summary.
VBA Functions
The functions described below can be used only in VBA code, not in worksheets. However, for each of these functions there exist equivalent worksheet functions.
Date returns the current date, while Time returns the current time. These two keywords (and these only, not the keywords Now , Timer , etc. described below) can also be used in variable assignments, and then they change the computer system's date and time. Here both character strings and Date values can be defined, such as Time= #8:30#: Date=#12/31/97# . The function Now returns the current date and time. Timer returns the number of seconds since midnight.
Please note that the three properties Date , Time, and Now are accurate only to the second. Timer has a somewhat finer division, changing 17 times per second under Windows 9x, and 100 times under Windows NT.
DateValue and TimeValue take as argument a character string in the language set under Windows and return their result in Excel's Date format. Thus DateValue("31 December 1999") returns 12/31/1999.
DateSerial and TimeSerial each take three arguments, year , month , day or hour, minute, second . The result is again a Date value. DateSerial(1997, 12, 31) returns 12/31/1997. These functions are extremely flexible in the evaluation of the parameter. Thus DateSerial(1997,13,1) returns 1/1/1998, DateSerial(1997, 2,31) returns 3/3/1998, DateSerial(1998,0,-1) returns 11/29/1997. Analogously, TimeSerial(4,-5,0) returns 3:55 (that is, five minutes before 4:00). In carrying out calculations (such as the current date plus one month) this flexibility is quite valuable .
Hour , Minute , and Second return the components of the time. Minute(#6:45:33#) returns 45. The time can also be given as a Date value or as a character string.
Year , Month , and Day are the equivalent functions for year, month (1 “12), and day (1 “31) of a date. Hence Month("1 April 1999") returns 4.
WeekDay functions like Day and returns the day of the week (1 = Sunday through 7 = Saturday). Alternatively, you can use the worksheet function Application.WorksheetFunction.WeekDay . This function is distinguished from the likenamed VBA function by a second optional parameter for the mode m . For m =2 the function returns the values 1 through 7 for Monday through Sunday, for m =3 the values 0 though 6. (See also the on-line help under Weekday .)
For calculations based on a 360-day year, which is used in some applications, there exists the function Application.WorksheetFunction. Days360 . This worksheet function returns the number of days between two dates on the basis of twelve months of thirty days each. If an optional third parameter False is given, then the function calculates according to the European method, otherwise (the default) according to the American. Details on this function can be found in the on-line help under Days360 . An example showing a result different from that obtained by direct subtraction of dates is Days360(#4/30/1999#, #5/31/1999#, False) : Calling this function returns the value 30, although there are 31 days between the ends of the two months.
For transformation of character strings and numbers into Date values and vice versa we have the functions CDate , CStr , CSng , and CDbl , defined in the previous section. CDate corresponds essentially to a combination of DateValue and TimeValue (since date and time are considered together). Here are two examples: CDate(34418.4) returns 3/25/1994 at 09:36 a.m., while CDbl(#31/12/1995 11:30#) returns 35064.479.
Calculating with Dates
The functions DateAdd , DateDiff , and DatePart , which have been available since Excel 4, have also made their way into VBA. DateAdd is used for adding one or more intervals of time to a time or date. The interval is given in the form of a character string: "yyyy" for years, "q" for quarters , "m" for months, "ww" for weeks, "y" , "w" , or "d" for days, "h" for hours, "n" for minutes, and "s" for seconds. The second parameter tells how often the interval should be added (with negative numbers you can even count backwards . However, only whole-number intervals are possible; half and quarter hours must be given in minutes.) The third parameter contains the starting time:
DateAdd("yyyy", 1, Now) ' date and time a year from now DateAdd("h", -2, Now) ' date and time two hours ago If invalid dates result from addition (such as 4/31), then Visual Basic returns the first valid previous date (4/30). Note that DateSerial behaves differently in such a situation, since DateSerial(1998, 4, 31) gives 5/1/1998!
With DateDiff you have a simple method of determining how many time intervals there are between two dates or times. The interval is given in DateAdd with a character string. The on-line help describes in detail how this function works. (In general, calculation is done in terms of the given interval. Therefore, the time difference between 1/31 and 2/1 is considered an entire month, while the much longer difference between 1/1 and 1/31 in considered 0 months.
DateDiff("m", Now, "1/1/1998") ' number of months until/from 1/1/1998 DatePart determines the number of periods for a particular point in time: In the case of years the calculation is from year 0, while with quarters, months, weeks, week of the year ( "ww" ), and day of the year ( "y" ) it is from 1/1 of the year. Then there is day of the month ( "d" ) (without the optional parameter this is Sunday) and day of the week ( "w" ). In the case of hours, calculation is from 0:00, while with minutes and seconds the calculation is from the last complete hour or minute. Thus DatePart in most cases fulfills the same functions as the already mentioned functions Year , Month , Day , Weekday , and so on.
DatePart("m", Now) ' number of months since 1/1 DatePart("y", Now) ' number of days since 1/1 DatePart("d", Now) ' number of days so far this month DatePart("w", Now) ' number of days so far this week Character Strings for Date and Time
The functions MonthName and WeekdayName are quite practical (new in Office 2000), returning character strings according to the regional setting of the local computer. For example, MonthName(2) returns the character string "February" , while WeekdayName(1) gives the result "Monday" .
With FormatDateTime the optional parameter yields the desired format ( vbGeneralDate, vbLongDate, vbShortDate, vbLongTime, vbShortTime ). In the USA standard setting for dates one has the following results:
For i=0 To 4: ?FormatDateTime(Now, i): Next 1/14/2000 3:11:05 PM Friday, January 14, 2000 1/14/2000 3:11:05 PM 15:11
| Tip | In the above example the year has four digits even in the case of vbShortDate . This is not always so, depending as it does on the system setting. They look this way on the author's computer, where years are always formatted with four digits. |
Worksheet Functions
The worksheet functions described below, apart from the exceptions Days360 and WeekDay, can be used only in worksheets. They correspond to various VBA functions described above, but in some cases have somewhat different names .
TODAY and NOW correspond to the VBA functions Date and Now , and they return the current date and, respectively, a combination of the current date and time.
DATE and TIME correspond to DateSerial and TimeSerial and form a date and a time from three values: year/month/day, and, respectively, hour/minute/second.
TIMEVALUE and DATEVALUE correspond to the like-named VBA functions. They transform character strings (such as "3-Aug-1978" ) into times and dates. If no year is given, then Excel automatically uses the current year. (This feature holds only for worksheet functions, not for VBA functions.)
YEAR , MONTH , DAY , HOUR , MINUTE , and SECOND as well as WEEKDAY and DAYS360 correspond to the VBA functions Year , Month , etc.
| Tip | The functions TODAY, NOW, DATE, DATEVALUE, TIME, and TIMEVALUE return numbers as their result. From these values a date or time is displayed in a cell only once that cell has been formatted in date or time format. |
One can give numerical date values (such as 34393.72917) or character strings (such as "28-February-1994 5:30") to these functions. (In worksheets the regional setting generally holds.) DATEVALUE and TIMEVALUE pay attention only to the information relevant to them. If date and time are to be read from a character string, then the formula DATEVALUE(x)+TIMEVALUE(x) is required.
| Tip | The input of dates into cells can be in the form 2/1/00 or even 2/1, if 2/1/2000 is meant and the current year is 2000. These input forms have the advantage that they can be used without the Shift key. |
Application and Programming Techniques
This section describes the application of the functions just introduced to both worksheets and VBA code. Sometimes the worksheet function will be given, and at others the equivalent function in VBA syntax. The worksheet functions for the techniques here described can be found in the example file DateTime.xls .
In general, dates and times can be used in a "normal" fashion. That is, dates can be compared, added, and subtracted. Note that Excel automatically inserts a date format into a cell containing a function that returns a date as result. This is usually practical, but it can be annoying, for example, when instead of the result 31 (a difference in times) the date 1/31/1900 is displayed. In such cases you will have to change the format of the cell. In the examples below, "d" represents a date.
Adding a Week to a Date
=d+7
Adding a Month to a Date
=DATE(YEAR(d), MONTH(d)+1, DAY(d)) ' worksheet formula DateSerial(Year(d), Month(d)+1, Day(d)) ' VBA
The above formula, for example, takes 3/23/1999 and returns 4/23/1999. There are exceptions to this rule when invalid dates come into the picture. From 1/31/1996 the result will be 3/2/1996 (because there is no 2/31/1996 in spite of the leap year).
Adding a Year to a Date
=DATE(YEAR(d)+1, MONTH(d), DAY(d)) DateSerial(Year(d)+1, Month(d), Day(d))
Number of Days in the Current Month
=DATE(YEAR(d), MONTH(d)+1, 1) - DATE(YEAR(d), MONTH(d), 1) DateSerial(Year(d), Month(d)+1, 1) - DateSerial(Year(d), Month(d), 1)
Number of Days in the Current Year
=DATE(YEAR(d)+1, 1, 1) - DATE(YEAR(d), 1, 1) DateSerial(Year(d)+1, 1, 1) - DateSerial(Year(d), c1, 1)
Number of Days in the Year Until a Given Date
=1+d-DATE(YEAR(d), 1, 1) 1 + d - DateSerial(Year(d), 1, s1)
Date of the Last Day of the Month
=DATE(YEAR(d), MONTH(d)+1, 0) ' worksheet DateSerial(Year(d), Month(d)+1, 0) ' VBA
Time Difference in Years (Age Calculation)
The formula Year(d2)-Year(d1)) is unsatisfactory for calculating a person's age. For example, Year("1/1/1999")-Year("7/1/1950") returns the result 49, even though the person is only 48 years old on 1/1/1999.
The solution to this problem is somewhat complex, but a simpler solution does not seem to be available. The difference will be corrected by 1 if the current date d2 is smaller than the birth date d1 (ignoring the year). To get the year out of the comparison, the year of date d1 is set to the same value as in d2 . Thus only month and day matter, not the year.
Returning to the example above, the difference in years amounts first to 49, but since 1/1/1999 is "less than" 7/1/1999, this value must be reduced by 1.
=YEAR(d2)-YEAR(d1)-IF(d2<DATE(YEAR(d2), MONTH(d1) , DAY(d1)), 1, 0)
The formulation in VBA is somewhat more complicated, but in exchange it is easier to read:
diff = Year(d2) - Year(d1) If d2 < DateSerial(Year(d2), Month(d1), Day(d1)) Then diff = diff - 1 End If
Time Difference in Months
The same problem appears in calculating with months. If the time difference between 1/25/1994 and 3/3/1994 is to be considered as two months, then you can use the first, simpler, formula. If the difference is to be considered as two months only from 3/25/1994, then you have to use the second formula (floating date boundaries):
=(YEAR(d2)-YEAR(d1))*12 + MONTH(d2)-MONTH(d1) ' month boundary at the 1st =(YEAR(d2)-YEAR(d1))*12 + MONTH(d2) - MONTH(d1)- ' month border IF(d2<DATE(YEAR(d2), MONTH(d2), DAY(d1)), 1, 0) ' as in d1
Date of the Next Monday
One is often faced with the problem that one is given a particular date, and from it one needs to calculate the date of the following Monday (or some other day of the week). The VBA formula for calculating the next Monday is as follows :
d = d + (9 - WeekDay(d)) Mod 7
Here is an example: Suppose that d is a Wednesday. Then WeekDay returns the value 4. Since (9 - 4) Mod 7 returns 5, an additional five days are added to the cur- rent date. If d was already a Monday, then the date does not change. If you wish to change the formula so that the following Tuesday, Wednesday, and so on should be calculated, then simply replace the number 9 by 10, 11, and so on.
Calculating with Times
In calculating with times you must consider whether you are dealing with a pure time between 00:00 and 23:59 (thus a numerical value between 0 and 1) or with a time value that also contains a date. In Excel's time format both times are displayed the same way, although in the first case only times on the day 1/1/1900 are considered, while in the second case the time can be on any date.
This difference has an effect, for example, if you wish to calculate the number of minutes since midnight. If you simply multiply the time value by 24*60, you will in the first case obtain the correct result, while in the second you have the number of minutes since 1/1/1900! The correct formula is thus = (x-TRUNC(x)) *24*60 , or, in VBA, (x-Fix(x))*24*60 .
Cell Formats for Times Larger Than 24 Hours / 60 Minutes / 60 Seconds
The integer part of a time is also problematic when the result of a calculation is to be expressed in hours/minutes/seconds, with results greater than 24 hours/60 minutes/60 seconds allowed. Thus 30 hours is represented internally as 1.24, and in the usual time format as 6:00. In such cases you must use a time format in which the format character for hours/minutes/seconds is placed in square brackets. The brackets cancel the otherwise valid time limits (24 h, 60 m, 60 s). Possible format strings are [h]:mm:ss , [m]:ss , and [s] . Square brackets for days and months are not allowed.
Time Differences Spanning Midnight
The calculation of the difference between two times that span midnight, say from 20:30 to 6:40, produces a negative value. This problem can be solved by the addition of 1 (corresponding to 24 hours):
=IF(t2<t1, 1+t2-t1, t2-t1)
If the date as well is stored in t1 and t2 (such as 3/30/1994, 20:30 to 3/31/1994, 6:40), then a simple difference suffices. However, the above formula presents no difficulties in this case, since in any case t2>t1 .
Holidays
A topic worthy of a separate discussion is holidays. In addition to all the other problems of working with dates, here we have the additional difficulty that the dates of many holidays change each year! For many planning tasks it is necessary that holidays be able to be calculated easily. The example file Holidays.xls contains the function Holiday(datum), which tests whether a given day is a holiday, and in that case returns the name of the holiday. See Figure 5-3. If the date is not a holiday, then an empty character string is returned.
Figure 5-3: The calculational basis for the holiday function
First some preliminaries : From an algorithmic point of view there are three types of holidays:
-
those that occur on the same date each year (such as Christmas)
-
those whose date depends on Easter (such as Pentecost, which is the seventh Sunday after Easter)
-
those whose date depends on another formula (such as Thanksgiving Day, which occurs on the fourth Thursday in November)
Every country has its own set of holidays. Furthermore, individual states, counties, or towns may celebrate their own holidays or have a special set of bank or school holidays. It is relatively easy using the program presented here to define a custom set of rules for holidays and to calculate these for each year anew.
The determination of Easter is a special case to be dealt with. (The occurrence of Easter Sunday depends on both the solar and lunar calendars. It occurs on the first Sunday after the first full moon after the vernal equinox!) Many mathematicians have already turned their attention to this problem. The following function calculates the date of Easter for an arbitrary year using an algorithm due to Gauss. Allegedly, this algorithm works correctly for dates through 2078 (I have not checked it, though for the next several years it does indeed work).
' example file Holidays.xls Function EasterDate (calcYear&) As Date Dim zr1&, zr2&, zr3&, zr4&, zr5&, zr6&, zr7& zr1 = calcYear Mod 19 + 1 zr2 = Fix(calcYear / 100) + 1 zr3 = Fix(3 * zr2 / 4) - 12 zr4 = Fix((8 * zr2 + 5) / 25) - 5 zr5 = Fix(5 * calcYear / 4) - zr3 - 10 zr6 = (11 * zr1 + 20 + zr4 - zr3) Mod 30 If (zr6 = 25 And zr1 > 11) Or zr6 = 24 Then zr6 = zr6 + 1 zr7 = 44 - zr6 If zr7 < 21 Then zr7 = zr7 + 30 zr7 = zr7 + 7 zr7 = zr7 - (zr5 + zr7) Mod 7 If zr7 <= 31 Then EasterDate = DateSerial(calcYear, 3, zr7) Else EasterDate = DateSerial(calcYear, 4, zr7 - 31) End If End Function
The second supplementary function is called NumberedWeekday . It calculates for a given year and month the first, second, third, fourth, last, second to last, weekday, say, the first Monday in May or the fourth Thursday in November. The algorithm is easy to understand. Beginning with the first or last day of the month, at most 28 days are run through forward or backward. If the weekday for this date coincides with the desired weekday, then dayCounter is increased by 1, respectively reduced by 1. If dayCounter coincides with the desired number of the weekday in question, then the day that was sought has been found.
Function NumberedWeekday (calcYear&, calcMonth&, calcWeekday&, _ number&) As Date Dim startdate As Date Dim i& Dim dayCounter& If number = 0 Then MsgBox "Invalid parameter in NumberedWeekday" Exit Function End If If number >= 1 Then 'first, second, third ... weekday in a month startdate = DateSerial(calcYear, calcMonth, 1) '1st day of month For i = 0 To 27 If Weekday(startdate + i) = calcWeekday Then dayCounter = dayCounter + 1 If dayCounter = number Then NumberedWeekday = startdate + i End If End If Next Else ' last, next to last ... weekday in a month startdate = DateSerial(calcYear, calcMonth + 1, 0) 'last day of m. For i = 0 To 27 If Weekday(startdate - i) = calcWeekday Then dayCounter = dayCounter - 1 If dayCounter = number Then NumberedWeekday = startdate - i End If End If Next End If End Function
HolidayTable(year) creates a list of all holidays for the given year and stores their dates and names in the two fields holidayDate and holidayName . These fields are then available to the Holiday function and need to be recalculated only when the year changes.
The calculation of this table of holidays is based on the worksheet function "holidays." This worksheet contains a list of all holidays, where the date must be given as an absolute date, a date relative to Easter, or the n th particular weekday of a given month. The holiday table can be easily altered to account for peculiarities of various countries or if some regulation changes the date of a particular holiday.
The majority of the code of the function HolidayTable is thus responsible for reading the holiday block beginning in cell A8. Since the properties CurrentRegion and SpecialCells do not function when HolidayTable is executed via Holiday during calculation in a worksheet, the size of the block must be determined in a For “Next loop. Otherwise, the example shows how the program can be kept readable by the use of object variables (such as holidaysRng for the range of cells with information on the holidays, rowRng for a column of this range).
Private Sub HolidayTable (calcYear&) Dim easter As Date Dim holidaysRng As Range, rowRng As Range Dim upperleft As Range, lowerleft As Range Dim ws As Worksheet Dim i& If Not IsNumeric(calcYear) Then Exit Sub ' holiday table has already been calculated If lastcalcYear = calcYear Then Exit Sub ' Easter formula is defined for this range If calcYear < 1900 Or calcYear > 2078 Then MsgBox "The algorithms for this program work only between " & _ "1900 and 2078" Exit Sub End If easter = EasterDate(calcYear) ' the sheet with the holiday data must be named "holidays" Set ws = ThisWorkbook.Sheets("holidays") ' the list of holidays starts at A8 Set upperleft = ws.[A8] ' but where does it end? ' SpecialCells may not be used out of a ' worksheet function (for unknown reason); ' therefore, we need a loop to find the end For i = 1 To 300 'there are certainly less holidays ... If upperleft.Offset(i, 0).Text = "" Then Set lowerleft = upperleft.Offset(i - 1, 0) Exit For End If Next Set holidaysRng = ws.Range(upperleft, lowerleft) ' loop for all lines of the holiday list ReDim holidayDate(holidaysRng.Rows.Count - 1) ReDim holidayName(holidaysRng.Rows.Count - 1) i = 0 For Each rowRng In holidaysRng.Rows holidayName(i) = rowRng.Cells(1, 1) If rowRng.Cells(1, 2).Text <> "" Then ' type 1: fixed date holidayDate(i) = DateSerial(calcYear, rowRng.Cells(1, 2), _ rowRng.Cells(1, 3)) ElseIf rowRng.Cells(1, 4).Text <> "" Then ' type 2: date relative to Easter Sunday holidayDate(i) = CDate(CDbl(easter) + rowRng.Cells(1, 4)) Else ' type 3: first/second/... weekday of month holidayDate(i) = NumberedWeekday(calcYear, rowRng.Cells(1, 5), _ rowRng.Cells(1, 6), rowRng.Cells(1, 7)) End If i = i + 1 Next rowRng ' save calcYear; thus, the holiday table has to be recalculated only ' if the year changes lastcalcYear = calcYear End Sub Once the fields holidaysDate and holidaysName exist, determining a holiday becomes child's play. All dates of the holidaysDate field are simply compared in a loop with the given date. If a match is found, the function returns the name of the corresponding holiday. Note the construction CDate(Int(dat)) : It accomplishes the eventual separation of the time from the date; the digits to the right of the decimal point are deleted, and then the resulting integer is again converted to a date.
Function Holiday (ByVal dat As Date) Dim i% If Year(dat) <> lastcalcYear Then HolidayTable Year(dat) dat = CDate(Int(dat)) ' eliminate the time For i = 0 To UBound(holidayDate()) If dat = holidayDate(i) Then Holiday = holidayName(i): Exit Function End If Next ' it is not a holiday Holiday = "" End Function
Calendar Generation
The function Holiday can be employed as a user-defined worksheet function as well as inserted into other VBA procedures. The procedure CreateCalendar shows how a new worksheet is introduced into the currently active workbook and a calendar inserted within it (Figure 5-4). A considerable part of the code is simply responsible for the formatting of the calendar. The procedure makes intensive use of With , in order to make access to individual cells more efficient and to make the code easier to read. With Application.ScreenUpdating = False is achieved that the screen is updated only at the end of the procedure. This speeds up the creation of the calendar. If the construction or formatting of calendars doesn't appeal to you, you can use the ideas of this procedure as a starting point for your own calendar procedures.
Figure 5-4: The calendar for the year 2000
Sub CreateCalendar () Dim i&, calcYear&, calcMonth&, calcDay& Dim holid$ Dim ws As Worksheet Dim start As Range Dim d As Date calcYear = InputBox("Please type in the year for the calendar!", _ "Create calendar", Year(Now)) Application.ScreenUpdating = False If Not IsNumeric(calcYear) Then Exit Sub ' create new worksheet in current workbook Set ws = Worksheets.Add() ws.Name = "Calendar " & calcYear ActiveWindow.DisplayGridlines = False Set start = ws.[A3] With start .Formula = calcYear .Font.Bold = True .Font.Size = 18 .HorizontalAlignment = xlLeft End With ' add month captions With start.Offset(1, 0) For i = 1 To 12 d = DateSerial(calcYear, i, 1) .Offset(0, i - 1).Formula = Format(d, "mmmm") Next End With ' format captions With Range(start.Offset(1, 0), start.Offset(1, 11)) .Font.Bold = True .Font.Size = 14 .Interior.Pattern = xlSolid .Interior.PatternColor = RGB(196, 196, 196) .HorizontalAlignment = xlLeft .Borders(xlTop).Weight = xlThin .Borders(xlBottom).Weight = xlThin .Interior.ColorIndex = 15 .Interior.Pattern = xlSolid .Interior.PatternColorIndex = 15 .ColumnWidth = 15 End With ' add dates For calcMonth = 1 To 12 For calcDay = 1 To Day(DateSerial(calcYear, calcMonth + 1, 0)) With start.Offset(calcDay + 1, calcMonth - 1) d = DateSerial(calcYear, calcMonth, calcDay) holid = Holiday(d) If holid = "" Then .Value = calcDay Else .Value = holid End If 'saturdays, Sundays, and holidays bold If holid "" Or Weekday(d) = 1 Or Weekday(d) = 7 Then .Font.Bold = True End If 'saturdays and sundays with grey background If Weekday(d) = 1 Or Weekday(d) = 7 Then .Interior.ColorIndex = 15 .Interior.Pattern = xlSolid .Interior.PatternColorIndex = 15 End If End With Next Next ' left alingment for all dates With Range(start.Offset(2, 0), start.Offset(32, 11)) .HorizontalAlignment = xlLeft End With End Sub Syntax Summary
In the following lines dt stands for a date or a time (variable type Date ), and str for a character string.
| VBA FUNCTIONS | |
|---|---|
| Date | returns the current date |
| Date = dt | changes the system date |
| Time | returns the current time |
| Time = dt | changes the system time |
| Now | returns date and time |
| Timer | returns seconds since 00:00 |
| DateValue(str) | transforms character string into a date |
| DateSerial(year, month, day) | combines three values into a date |
| Year(dt) | returns the year |
| Month(dt) | returns the month (1 “12) |
| Day(dt) | returns the day (1 “31) |
| WeekDay(dt) | returns day of week (1 “7 for Sun “Sat) |
| WorksheetFunction.WeekDay(dt, 2) | returns day of week (1 “7 for Sun “Sat) |
| WorksheetFunction.WeekDay(dt, 3) | returns day of week (0 “6 for Mon “Sun) |
| WorksheetFunction.Days360(dt1, dt2) | difference between days in 360-day year |
| WorksheetFunction.Days360(dt1, dt2, False) | as above, European mode |
| TimeValue(str) | changes character string into a time |
| TimeSerial(hour, minute, second) | combines three values into a time |
| Hour(dt) | returns hour (0 “23) |
| Minute(dt) | returns minute (0 “59) |
| Second(dt) | returns second (0 “59) |
| CDate(v) | change into Date -Format |
| CStr(dt) | change into character string |
| CSng(dt) | change into single-precision floating point number |
| CDbl(dt) | change into double-precision floating point number |
| WeekdayName(n) | returns character string with name of day of week (1 corresponds to Monday, 7 to Sunday) |
| MonthName(n) | returns character string with name of month |
| FormatDateTime(d, type) | returns character string with date or time (type=vbGeneralDate / vbLongDate / vbShortDate / vbLongTime / vbShortTime) |
| WORKSHEET FUNCTIONS | |
|---|---|
| TODAY() | current date |
| NOW() | current time |
| DATE(year, mon, day) | combines three values into a date |
| DATEVALUE(str) | changes a character string into a date |
| YEAR(dt) | returns year |
| MONTH(dt) | returns month (1 “12) |
| DAY(dt) | returns day (1 “31) |
| WEEKDAY(dt) | returns day of week (1 “7 for Sun “Mon) |
| WEEKDAY(dt, 2) | returns day of week (1 “7 for Mon “Sun) |
| WEEKDAY(dt, 3) | returns day of week (0 “6 for Mon “Sun) |
| DAYS360(dt1, dt2) | difference between dates in 360-day year |
| DAYS360(dt1, dt2, False) | as above, European mode |
| TIME(st, min, sek) | combines three values into a time |
| TIMEVALUE(str) | combines three values into a time |
| HOUR(dt) | returns hour (0 “23) |
| MINUTE(dt) | returns minute (0 “59) |
| SECOND(dt) | returns second (0 “59) |
EAN: 2147483647
Pages: 134