BIGmiralli Frequent Poster
Joined: 17 Apr 2007 Posts: 38 Location: Boston, Massachusetts
|
Posted: Fri May 23, 2008 12:54 pm Post subject: 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:-
Code: |
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.
Code: | 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 |
Here is a oneliner version of this:
Code: | =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
________
Honda xl100
Last edited by BIGmiralli on Wed Feb 16, 2011 10:43 am; edited 1 time in total |
|