howto extract number of hours on a date&time field

[mjvnotch]

Hi! I hope you can help me with this one.

I'm having a problem on how to extract the number of hours by subtracting field2 by field1 with a combination date & time format.

given two fields:

i.e.
field1 = 2000-05-15 02:17:22
field2 = 2000-05-14 23:24:20
---------------------
total time = 2h:53m:2s

I'm interested on how to extract the total time result and particularly the hours portion.

Thanks in advance

[omarswan]

Hi,
Are you trying to find the difference between to two dates?

[Ianpbaker]

Try using the Hour,Day and Month Functions. THey return the individual values from a date

month("24/05/00 5:15") returns 5
day("24/05/00 5:15") returns 24
hour("24/05/00 5:15") returns 5

alternatly use the datediff function

Hope this helps

Ian

[Edited by Ianpbaker on 05-24-2000 at 01:40 PM]

[mjvnotch]

I'm trying to extract the hours and minutes section for calculation purposes.

You see i'm trying to build a program that charges the online usage of an isp client by calculating the number of hours and minutes used.

Unfortunately the network monitoring software stores the login start-time and stop-time in the format:

yyyy/mm/dd h:mm:ss

So i'm particularly interested in extrating the hours and minutes portion of the field above.

[Ianpbaker]

as I said, then use the hour and minute functions to grab the values from any dates, theese will return in an integer format and then you can do your calculations.

Hope this Helps

Ian

[Mongo]

This might give you a few ideas. If you're not worried about times exceeding 99:59:59 the format option would work, else the rest of the story...

Dim str1 As String, str2 As String, str_res as string
Dim dt1 As Date, dt2 As Date, val As Double
Dim hr As Long, mn As Integer, sc As Integer
  str1 = "2000-05-15 02:17:22"
  str2 = "2000-05-14 23:24:20"
  dt1 = CDate(str1)
  dt2 = CDate(str2)
  val = dt1 - dt2
  ' option 1
  str_res = Format(val, "hh:mm:ss")
  ' option 2
  hr = val * 24
  mn = (val * 1440) - (hr * 360)
  sc = (val * 86400) - (hr * 360) - (mn * 60)
  str_res = hr & "h:" & mn & "m:" & sc & "s"