Calculating With Dates
You may want to know how many days are between two dates, or
work out how old someone is given their date of birth. The DateDiff function of
VB / VBA gives you just this ability. It's format is
| DateDiff(interval,Date1,Date2,[,
firstdayofweek[, firstweekofyear]]) |
Ignore the last two parts as
they are seldom used and are not required. The interval part is the time
interval you want to measure. The possible values are:-
| Interval | Value | | Interval | Value
|
| yyyy |
Year |
| w |
Weekday |
| q |
Quarter |
| ww |
Week |
| m |
Month |
| h |
Hour |
| y |
Day of year | | n |
Minute |
| d |
Day |
| s |
Second |
These
are the same as the format function constants. Suppose we have someone's
date of birth and want to know how old they are in years then we would use the
following
| DateDiff("yyyy",DateofBirth,Date()) |
Now there is an inherent bug in the DateDiff function (which Microsoft of course
call a feature). If your date of birth is say 14th March 1971 and today's
date is 10th Febuary 2000, DateDiff will return 29, the wrong age. This is
because it counts the number of times it passes January first rather than actually
working out the difference in years. So to counter this it is advisable if you
are using DateDiff to use a wrapper function to sort out wrong dates. Below is
the function I normally use.
Public Function AgeInYears(date1 As Date, date2 As Date)
As Long
AgeInYears = DateDiff("yyyy", date1,
date2)
If Format(date1, "mmdd") >
Format(date2, "mmdd") Then
AgeInYears =
AgeInYears - 1
End If
End Function |
Michael Barron of Cornerstone Solutions has provided us with a oneliner version of this
| =Fix(DateDiff("yyyy",[txtBirthdate],Now())+Int(Format(Now(),"mmdd")<Format([txtBirthDate],"mmdd")))
|
If you want to calculate how many seconds between two times then use
| DateDiff("s",DateorTime1,DateorTime2) |
If you want to add a certain amount of days to a date then use DateAdd or
just add numbers. 1 is equal to 1 day, so 1/24 is equal to 1 hour. So either of
the following adds 7 Hours to the current date and time.
DateAdd("h",7, Now())
OR
Now() + 7/24 |
|
