Microsoft Access Office VB VBA Help and Examples
           
             

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:-

IntervalValue    IntervalValue
yyyy Year  w Weekday
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
 
HOME   SEARCH SITE   PRIVACY POLICY   CONTACT
The code and application content of this site is copyright of Smiley I.T. and as such reproduction in any form which is for commercial use requires the permission of the Webmaster. Any use of this code for non-commercial use only requires a link or comment back to the original page you took the code from.