IN THE SPOTLIGHT: MDE to MDB Conversion Service
(also supports: ACCDE to ACCDB, ADE to ADP, etc)
IN THE SPOTLIGHT: vbWatchdog
VBA error handling just got easier...
" vbWatchdog is off the chart. It solves a long standing problem of how to consolidate error handling into one global location and avoid repetitious code within applications. "
- Joe Anderson,
Microsoft Access MVP
Meet Shady, the vbWatchdog mascot watching over your VBA code →
(courtesy of Crystal Long, Microsoft Access MVP)
IN THE SPOTLIGHT: vbMAPI
An Outlook / MAPI code library for VBA, .NET and C# projects
Get emails out to your customers reliably, and without hassle, every single time.
Use vbMAPI alongside Microsoft Outlook to add professional emailing capabilities to your projects.
Provided by Allen Browne, November 2003. Updated March 2007.
How do you calculate the difference between two date/time fields, such as the hours worked between clock-on and clock-off?
Use DateDiff() to calculate the elapsed time. It returns whole numbers only, so if you want hours and fractions of an hour, you must work in minutes. If you want minutes and seconds, you must get the difference in seconds.
Let's assume a date/time field named StartDateTime to record when the employee clocks on, and another named EndDateTime for when the employee clocks off. To calculate the time worked, create a query into this table, and type this into the Field row of the query design grid:
Minutes: DateDiff("n", [StartDateTime], [EndDateTime])
Minutes is the alias for the calculated field; you could use any name you like. You must use "n" for DateDiff() to return minutes: "m" returns months.
To display this value as hours and minutes on your report, use a text box with this Control Source:
=[Minutes] \ 60 & Format([Minutes] Mod 60, "\:00")
This formula uses:
Do not use the formula directly in the query if you wish to sum the time; the value it generates is just a piece of text.
If you need to calculate a difference in seconds, use "s":
Seconds: DateDiff("s", [StartDateTime], [EndDateTime])
You can work in seconds for durations up to 67 years.
If you need to calculate the amount of pay due to the employee based on an HourlyRate field, use something like this:
PayAmount: Round(CCur(Nz(DateDiff("n", [StartDateTime], [EndDateTime]) * [HourlyRate] / 60, 0)), 2)
Note: Access 97 and earlier do not have the Round() function, but you can download one.
Doug Steele and Graham Seach have developed a more complete DateDiff. It shows the difference between two date/time values in days and hours and minutes and seconds.
To compare a date/time value to now, and return something readable such as "next week", or "in 5 hours", or "2 years ago", see Constructing Modern Time Elapsed Strings.
|Home||Index of tips||Top|
This is a cached tutorial, reproduced with permission.
Have your say - comment on this article.
What did you think of 'Calculating Elapsed Time'?
28 August 2009
thank you for this... cool!!!
Have your say...