Microsoft Access Office VB VBA Help and Examples
           
             

Calculating and Formatting Dates and Times

This section tells you all about Access and Dates and Times and some of the formulas you can use with them. First of all lets talk about how access understands a date or time.

Access thinks that 1 is one day. So using this formula if we add 7 to a date we are adding one week. Access also stores dates as numbers where the date it works from is 30th December 1899, which is equal to zero, and guess what .... 29th December 1899 is -1. 

Now before you start worrying about converting all these numbers e.t.c don't worry. It is very rare that you will need to do this. But having this knowledge can save considerable amounts of time when calculating with dates. So lets start first with displaying dates. We can format a date/time using the Format command

Format(#14/03/71 19:00:00#,"Long Date")

gives 14 march 1971, as we have asked for a date format it drops the time. Note the use of the # symbol. You can use speechmarks instead but it's worth getting use to using # as it tells Access that the figure enclose is a date/time. Other options for the second part of the function are...

Format Result
"General Date" 14/03/1971 19:00:00
"Short Date" 14/03/1971
"Long Time" 19:00:00
"Medium Time" 07:00
"Short Time" 19:00
"DD,MMM,YYYY" 14,Mar,71
"hh.nn.ss" 19.00.00
"MMMM/YY" March/71

You can see from the last three that you can make up your own formats. Look up date formats in the help for more examples.
So how do we calculate with dates.

Basic Addition and Subtraction
If 1 = one day then 1/24 = 1 hour (1/24)/60 = 1 minute and  ((1/24)/60)/60) = 1 second
So #14/03/1971# - 1   will give  13/03/1971

If you want an accurate method use DateAdd
DateAdd("n",3600,#14/03/1971#) gives 16/03/1971 12:00:00 
We have added 3600 minutes. Note Minutes is specified by "n" as "m" is months.

If you want to work out the difference between two dates then use DateDiff
DateDiff("n",#13/03/1971#,#14/03/1971#) gives 1440
We have calculated the number of minutes between two dates. You could calculate the number of year , months , days hours e.t.c

and last but not least if you want to work out the number of shift hours worked but it may be the case that this sometimes rolls over midnight then...
IIf([StartTime]<[EndTime],DateDiff("h",[StartTime],[EndTime]),24-DateDiff("h",[EndTime],[StartTime]))

 

Hopefully this little insight gives you an idea of what you can do. For more information look in the Access help for the individual functions.

 
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.