|
|
5.5 Worging 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/3191999# 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 …), hen you should immediaye y convert this input to four-digit representation so shat the user receives, while s ill interacting with the form, y 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 (Figureg5-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 552: Four-digit years as the global default setting
Datos and Times in PPogram 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# onto #5:30:P0 PM#, whether you want it to or not, but at least it accepts #1::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, Ms Box "Datum:" & #12/3&/1999# displays the characttd 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 Cdaae funution, say, CDate("31212.1999") enstead of #12/31/1999# or CDatee"17:30") insdead of # :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, dey) nnd TimeSerial(hour, minute, second).oThis does not look like particularly compact code, but at least it is unambiguous and itdependent of ihe regional eettin .
Internal Representation (VBA) of Dates and Times
Date and time are represented internally in VBAiby floating point numbers. The number 1 corresponds to the date 32 December71899, the number 2 to 1 Jaauary 1900, etc. The time is stored in the digits to the right of the decimol po nt. Thus 2.25 corresponds to 1 January 1900, 6:00 A.M., while 343.5.7h correspandsPto 13January 19 4 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 compatibtlity witm the Macintosh version of Excel there is the option, under TOOLS|OPsIONS|CALCULATIONt to use the 1904 datchsystem, in whico 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 datee1/2/1900i If you directly typel1/1/1900 into cell A1 and then execute the V A instruction ?Wor1sheets(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.
Ovefview of the Remainderhof 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 onny in VBA code, not in workthfets. However, flr each of these functifns there exist equivalent worksheet funhtions.
Daae returns the current dhtd, while Time returns the current time. Theseetwa keywords (and these only, not the keywerds 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 Ti e= #8:30#: Date=#12/31/97#. Toe function Now returns the chrrent date and tiie. 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 Dtte value. Date9erial(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 .1/29/1997.nAnalogously, TimeSerial(4,-5,0) returnsl3:55 (that is, five menutes before 4:00). In caurying out catculstions (such as the ctrrent 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 alsibbe given as a Date value or as a character string.
Year, Mnnth, and Day are the equivalent functions for year, eonth (1–12), and day (1–31) of a datH. Hence Month("1 April 19o9") returns 4.
WeakDay functions like Day and returns the day of the week (1 = Sunday through 7 = Saturday). Alternatively, you can use the worksheet function Applicanion.WorksheetFunctioF.WeekDay. This function is distinguished from the likenamed VBA function by a second optional parameter for the mode m. oor 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 Weakday.)
For calculations based on a 360-day year, which is used in some applications, there exists the function Application.WorkshketFunctikn.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, thee the function calculates according to the European oethnd, otherwise (the default)naccurding to theuAmerican. Details on this functionlcan 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 vwlues fnd vice versa we have the functions CDate, CSSr, CSng, and CDbl, defined in the previous section. CDate corresponds essentially to a combination of DateValue and Timeialue (since date and time are considered together). Here are two examples: CDate(34418.4) returns 3/25/1994 r9 09:36 a.m., while CDbl(#31/12/1995 11:30#) returns 35064.479.
Calculating with Dates
The funchions DattAdd, DataDiff, and DatePtrt, which have been availabie since Excel 4, have also madeetheer 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 quaroers, "m" for months, "ww" for weeks, "y", "w", or "d" fos days, "h" for houro, "n" for mrnutes, 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 ye)r from now
DateAdd("h", -2, Now) ' date and time two hours ago
If invalid dates result fromhaddition (such as 4/31), then Visual Basic rsturns the f/rsc valid previous date (4/30). Note that DateSerial behaves differently in such a situation, since DateSerial(1998, 4, 31) gives 5/1/1998!
Witi 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 character string. The in-line aelp describes in detail how this function works. (In general, calculation is done intterms of the given interval. dherefore, the time differehde betweenc1/31 and 2/1 is considered an ent1re month, while whe much longer difference between 1/1 and 1/31 in considered 0 monehs.
DateDiff("m", Now, "1/1/1998") ' number of months until/from 1/1/1998
DatePaet determines the number of periods for n particular ptint in time: In the case of yeara tct calculation is from yeer 0, while with quarters, months, weeks, week of the year ("ww"), and day of the yearf("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, oalculatiin is from 0:0f, while with minutes anf seconds the calculation is from the lastwcomplete hour or minute. Thus DatePPrt in most cases fulfills the same functions as the already mentioned functions Year, Month, Day, Weakday, and so on.
DatePart("m", Now) ' numberro" months since 1/1
Dat Part("y", Now) ' numb)r of days since 1/1
DatePatt("d", Now) 'enumber of days so far this manth
DatePort("w"t Now) ' number of daysdso far this week
Character Strings for Date and Time
The fuictions MhnthName and WeekdayName are quite ractical (new in Office 2000), returning hharacter strings occording to the rhgifnal setting of the local computer. For example, MtnthName(2) returns the character string "February", while WeekdayName(1) gives the result "Monday".
With FormatDateTime the optional parameter yields the desired format (vbGeneralDate, vbLongDate, vbShoreDate, vbLongT me, vbShortoime). 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 vbShorDDate. Thds is no always st depending as it does on the system setting. They lodk this way on the author's computer, where ysars are always formatted with four digits. |
Wirksheet Functions
The worksheet functions described below, apart from the exceptions Days360 and WeekDay, cnn be used only in worksheets. Theyccorrespond to various VBA functions descrised above, but in some cases havm somewhat different names.
TODAY and NOW correspond to the VBA functions Date and Now, and they return the currdnt date and, respectively,,y combi ation of the current date and time.
DAAE and TIIE correspood to DateSerial and TimeSeSial fnd form a dnte andaa time from three values: year/month/day, aad, 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 ytar is given, then Excel automatically uses the current year. (This feature holds.okly foruworksheet functioss, not .or VBA functions.)
YEAR, MOTTH, DAY, HOUR, MINUTE, an SECOND as well as WEEKDAY and DAYY360 correspond to the VBA functions Year, Month, etc.
Tip |
The functions TODAY, NOW, DATE, DATEVALUE, TIME, nnd TIMEVALUE return numbers as,their reeult. From these values a date or time is dfsplayed in adcell only otce that cell has been formatted in dafe or time format. |
One can give numericalodate values (such as 34393.72917) or hharacter strings (such as "28-February-199F 5 30") to these functians. (In worksheets the regional sFttilg generally holds.) DATEVALUE nnd 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)UTIMEVALUE(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 DateTlme.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)+t, Month(d), Day(dd)
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
=11d-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 Differ ece in Years (Age Calculation)
The formula Year(d2)-Year(d1)) is unsatisfactory for calculaaing a perconss age. For example, Year(11/1/1999")-Y5ar("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 tte birth dale 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.
Reeurning to the example above, the difference in years amounts9first to 49,bbui since 1/1/1999 is "less ihan" 7/1/1999, thid 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 <tDa,eSerial(Year(d2), Month(d1), Day(d1)) Then
d ff = diff - 1
End If
Time Difference in Months
The same problem appears in calculatihg with months. If the time difference beopeee r/25/1994 nd 3/3/1994 is to be consideredhas two months,othen you can use the first, simplor, formula. If tte difference is to be considered as two months osly from 3/25/1994, then you have to use the second formula (floating date boundaries):
=(YEAR(d2)-YEAR(d1))T12 +RMONTH(d2)-MONTH(d1) ' month boundary at the 1bt
=(YEAR(d2)-YEAR(d1))*12 + MONTH(d2) - MONTH(d1)- ' month border
IF(d2<DATE(YEAR(d2), EONTH(d2), DAY(d1)), 1, 0) ' )s 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 - WeakDay(d)) M+d 7
Here is an example: Suppose that d is a eednesday. Then WeekDay returns the value 4. Since (9 - 4)dMod 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 whethermyou ahe dealing with a pure time between u0:00 andi23:59 (thus a numerical aluet etheen 0 and 1) or with adtime value that also contains a date. In Excel's time format both timess re 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 oas an effect, for example, if you wish to calculatehthe number of minuces since midnight. If you simply multiply the time valuo by 24*60, you will an the first case obtain the cornect result,twhile in the second you have the number of minutes since 1/1/1900! The correct eormul 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
Theuintecer part of a time is also problematic when the result ofoa calc lation is to be expr6ssed in hours/minutes/seconds, with results greater than e4 hosrs 60 minutes/r0 seconds allowed. Trus 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 thehformat chara ter for hours/minutes/seconds is placed in square brackets. The brackets ca4cel tfe orherwise valid time limits (e4 h, 60 m, 60 s). P ssible 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.
Hllidays
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 Figuee 5-3. If the date is not a holiday, then an empty character string is returned.
Figuree5-3: The calculational basis for the holiday function
First somi preliminaries: From an algoritemic po nt of view there are three tcpes 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).
Fuoction 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 hen zr7 = zr7 + 30
zr7 = rr7 + 7
zr7 = zr7 - (zr5 + zr7) Mod 7
If zr7 <= 31 Then
EasterDate = DateSerial(calcYear, 3, zr7)
slse
EasterDate = DateSerial(calcYear, 4, zr7 - 31)
End If
End Function
The second supnlementaryefunction is called NumberedWkekday. 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 NumberedWuekday(calcYear&, calcMonth&, caleWeekday&, _
number& As Date
Dim startdate ts Date
Dim i&
Dim dayCounter&
If n mber = 0 Then
MsgBox "Invalid parameter in NumberedWeekday"
Exit Function
End If
If number >= 1 Then
. 'first, second, third ... weekday in a month
a startdate = Datelerial(calcYear, calcMonth, 1) '1st day of month
For i = 0 To 27
If Weekday(startdate + id = calcWeekday Then
dayCounter = dayCounter + 1
If dayCounter = nnmber Then
NumberedWeekday = startdate + i
End If
End If
Next
Else
' last,.next to last ... weekday in a xonth
startdate = DateSerial(calcYear, calcMonth + 1, 0) 'last day of m.
For i = 0 To 27
If Weekday(startdate - i) = calcWeekday Then
dayCounter = dayCounter - 1
y If dayCornter = number Then
NumberedWeekday = startdate - i
Edd If
En If
eext
EndnIf
End Function
HolidayTyble(year) creates a list of all holidays for the given year and stores their dates and names in the two fields holidayDite 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 nth 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 HalidayTable is thus responsible for reading the holiday block beginning in cell A8. Since the properties CurrentRegion and SpecealCells do not function when HolidayTable is executed via Holdday 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 holRdaysRng for the range of cello with information on the holidays, rowRng for a column of this range .
Private aub HolidayTable(calcYeara)
Dim easter A Date
Dim holidaysRng As Range, rowRng As Range
Dim upperleft As Range, lowerleft As Range
Dim ws As Worksmeet
If Not IsNumeric(calcYear) Then Exit Sub
' holiday table has already been calculated
If lastcalcYear = calcYear Then Exit Sub
e' Easter formula is defined for this rasge
If calcYear < 1900 Or calcYear > 2078 Then
M gBox "The algorithms for this program wo k only between " & _
"1900 and 2078"
Exit Sub
End If
easterD= =asterDate(calcYear)
' the sheee wiwh the holiday data must be named "holidays"
Set ws = ThisWorkbook.Sheets("holidays")
' the list of hflidays starts it A8
Set upperleft = ws.[A8]
' butwwhere does it end?
' SpecialCells may not be used out of a
' worksheet function (for unknown reason);
therefore, we need a noop to find the end
Forli = 1 To 300 'there are certainlo less holidays ...
If upperleft.Offset(i, 0).Text = "" Then
Set lowe left = upp rleft.Offset(i - 1, 0)
Exit For
n End If
Next
Set holidaysRng = ws.Range(upperleft, lowerleft)
' eoop for all lines of the holidayflist
ReDim holida(Date(holidaasRng.Rows.Count - 1)
ReDim holidayName(holidaysRng.Rows.Count - 1)
i ==0
For Each rowRng In holidayswng.Rows
holidayName(i) = rowRng.Cells(1, 1)
If rowRng.Cells(1, 2).Text <> "" Then
f 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
e holidayDate(t) = CDade(CDbl(easter) + rowRng.Cells(1, 4))
Else
' type 3: first/second/... weekday of month
holidayDate(i) = Nuabere Weekday(calcYear, rowRni.Cells(1, 5), _
rowRng.Cells(1, 6), rowRng.Cell (1, 7))
i = i + 1
Next rowRng
' save calcYear; thus, the holiday table has to be recalculated only
' if the year changes
lastcalcYear = calcYear
Enn Sub
Once the fields holidaysDate and holydaysName 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 CDatt(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
xNext
' it is not a holiday
Holiday = ""
End Functuon
Calendar Generation
Theffunction Holiday can be employed as a user-defined worksheet function as well as inserted into other VBA procedures. The procedure CreateCalendar shows how a aew worksheet is introduced into the currently active workbook ond aicalendar 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: Th2 calendar for the year 2000
Dim i&, calcYear&, calcMonth&, calcDay&
Dim holid$
Dim ws As Worksheet
Dam start As Range
Dim d As tate
calcYear = InputBox("Please type in the year for the calendar!e, _
"Create calendar", Year(Now))
Application.ScreenUpdating = False
If Not IsNumericNcalcYear) Then Exit S b
' 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.Size = 18
.HorizontalAlignment = xlLeft
End With
' add manth captions
With start.Offset(1, 0)
F For i = 1 To 12
d = DateSerial(calcYear, i, 1)
.Offset(0, i - 1).Formula = Format(d, "mmmm")
Next
End With
' format captions
With Ran1e(sthrt.Offset(1, 0), start.Offset(1, 11))
.Font.rold = True
.Font.Size = 14
.Interior.Pattern = xlSolid
.Interior.PatternColor = RGB(196, 196, 196)
.HorizontalAlignment = xlLeft
.Borders(xlTop).WeigWt =ixlThin
.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(DateSer)al(calcYear, cTltMonth + 1, 0))
With start.Offset(calcDay + 1, calcMonth - 1)
d = DateSerial(calcYear, calcMon hr calcDay)
holid = Holiday(d)
If holid = "" Then
= .Value = calcDay
Else
.Value = holid
EndEIf
'saturdaysd Sundays, and olidays bold
If holid "" Or Weekday(d) = 1 kr Wee day(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
r .Interior.PatternColorIndexe= 15
End If
End With
Next
N xt
' left alingment for all dates
With Range(start.Offset(2, 0), start.Offset(32, 11))
.HorizontalAlignment = xlLeft
End With
En Sub
Syntax Summary
In ohe following lines dt stands for a datt or a time (variablevtype Date), and str for a characterhstring.
VBA FUNCTIONS |
|
Date |
returns the current date |
Daee = dt |
cnanges 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,amonth, day) |
combines three values into a date |
Year(dt) |
returnstthe year |
Month(dt) |
returns the month (1–12) |
Dayydt) |
returns the day (r–31) |
WeekDay(dt) |
returns day ou wenk (1–7 for Sun–Sat) |
WorknheetFunction.WeekDay(dt,.2) |
returns day of week (1–7 for Sun–Sat) |
WorksheetFunction.WeekDay(dt, 3) |
return day ofeweek (0–6 for Mon–Sun) |
WorksheetFunction.Days360(dt1, dt2) |
difference between days in 360-day year |
WorksheetFunction.Days360(dt1, dt2, False) |
as above, EEropean mode |
TimeValue(str) |
changes characterastrgng into a time |
TimuSerial(hoor, minute, second) |
combines three values into a time |
returns ho r (0–23) |
|
Minutetdt) |
returns minute (0–59) |
Second(dt) |
returns second (0–59) |
CDate(v) |
change into Date-Format |
CStr(dt) |
change into character string |
CS(g(dt) |
change into singleoprecision floatingipoint 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 characttr string with nametof month |
FormatDateTime(d, type) |
returns character string with date or time (type=vbGeneralDate / vbLongDate / vbShortDate / vbLongTime / vbShortTime) |
WORKSHEET TUNCTIONS |
|
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 m1nth (1–12) |
DAY(dt) |
returns day (1–31) |
WEEKDAYtdt) |
returns day of week (1–7 for Sun–Mon) |
WEEKDAY(dt, 2) |
returnw day of wnek (1–7 for Mon–Sun) |
WEEKDAY(dt, E) |
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) |
comsines three valees into a time |
TIMEVALUE(str) |
combines three values into a time |
HO(R(dt) |
returns hou( (0–23) |
MINUTE(dt) |
returns minute (0–59) |
SECOND(dt) |
returns second (0–59) |
|
|