Manufacturing Information Solutions Forum Index Manufacturing Information Solutions
Your Place for Support and Discussions
 
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Calculating With Dates

 
Post new topic   Reply to topic    Manufacturing Information Solutions Forum Index -> Microsoft Access
View previous topic :: View next topic  
Author Message
BIGmiralli
Frequent Poster


Joined: 17 Apr 2007
Posts: 38
Location: Boston, Massachusetts

PostPosted: Fri May 23, 2008 12:54 pm    Post subject: Calculating With Dates Reply with quote

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
Back to top
View user's profile Send private message
dawn
Master Poster


Joined: 26 Jun 2004
Posts: 311
Location: Mishawaka, IN

PostPosted: Wed Sep 17, 2008 1:32 pm    Post subject: Reply with quote

Cool Exclamation
_________________
Dawn Mitchell
Manufacturing Information Solutions
www.mis-group.com
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Post new topic   Reply to topic    Manufacturing Information Solutions Forum Index -> Microsoft Access All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group