Time Formatting

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello all,

I am new to MS Access. I am trying to calculate the
difference in HH:MM:SS between two dates. When I use the
time formats availalble in Access it does not go beyond
24hrs, i.e. if the start period was Jan 1st 2003 11:00am
and the end time was Jan 3rd 2003 11:30am it should show
me 48:30:00 however it just show 00:30:00. Any assistance
would be welcomed.

Barry
 
Use the DateDiff function to calculate date and/or time differences. This
function is explained in Help file.

Time in ACCESS is stored as a fraction of a 24-hour day; thus, midnight is
0.000000000000, noon is 0.50000000000, 6 pm is 0.750000000000, etc.

Post back if you have questions.
 
I am new to MS Access. I am trying to calculate the
difference in HH:MM:SS between two dates. When I use the
time formats availalble in Access it does not go beyond
24hrs, i.e. if the start period was Jan 1st 2003 11:00am
and the end time was Jan 3rd 2003 11:30am it should show
me 48:30:00 however it just show 00:30:00. Any assistance
would be welcomed.
Click to expand...

To add to Ken's posting: There is no way to calculate more than 24 hours
unless you include the date and time. To format into seconds you'll need to
calculate the seconds then divide the time up into hh:nn:ss (notice that
minutes is nn, not mm, which would be months). The following function is
adapted from a KB article and will give you exactly what you want:

Function GetElapsedTime(interval)

Dim TotalHours As Long
Dim TotalMinutes As Long
Dim TotalSeconds As Long
Dim Days As Long
Dim Hours As Long
Dim Minutes As Long
Dim Seconds As Long

Days = Int(CSng(interval))
TotalHours = Int(CSng(interval * 24))
TotalMinutes = Int(CSng(interval * 1440))
TotalSeconds = Int(CSng(interval * 86400))
Hours = TotalHours Mod 24 + (Days * 24)
Minutes = TotalMinutes Mod 60
Seconds = TotalSeconds Mod 60

GetElapsedTime = Format(Hours, "00") & ":" & _
Format(Minutes, "00") & ":" & Format(Seconds, "00")

End Function

Pass it the 2 dates like this:

?GetElapsedTime(Now - #12/28/03#)
07:43:39
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
You can always try the example that Doug Steele has posted on his web site:

A More Complete DateDiff Function
http://members.rogers.com/douglas.j.steele/Diff2Dates.html

Create a new module, copy and paste the code. Note: Make sure you have Option Explicit
included at the top of your new module, as the second line of code, ie:

Option Compare Database
Option Explicit

Then, as an example of using this function you can copy the following into the debug
window (activated via Ctrl + G):

Input:
? Diff2Dates("hn", #1/1/2003 11:00:00 AM#, #1/3/2003 11:30:00 AM#)

Returns:
48 hours 30 minutes

Tom
______________________________________________


Hello all,

I am new to MS Access. I am trying to calculate the
difference in HH:MM:SS between two dates. When I use the
time formats availalble in Access it does not go beyond
24hrs, i.e. if the start period was Jan 1st 2003 11:00am
and the end time was Jan 3rd 2003 11:30am it should show
me 48:30:00 however it just show 00:30:00. Any assistance
would be welcomed.

Barry
 
Back
Top