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