Difference in Time 1

[nat1967]

Hi group,

I have two fields that hold Time data. There are corrosponding date fields also. I was going to use a time stamp like Now() but the users said they need to be able to input the time since the ticket might not get opened immediately.

txtDateIssueIn and txtDateIssueClosed
txtTimeIssueIn and txtTimeIssueClosed

As long as the issue is closed same day, i can calculate the difference in time. However, if the problem goes on past midnight, WHOA!! ....

Example:

txtTimeIssueIn = 9:00 am
txtDateIssueIn = 5/16/2003

txtTimeIssueClosed = 10:30 am
txtDateIssuedClosed = 5/16/2003

Difference: 1.5 hours

but... what if txtDateIssuedClosed is 5/17/2003. that is where my problem comes in. DateDiff doesnt seem to handle next day calculations or maybe.... could it be.... i dont handle time well....hmmm...

Can anyone point me in the direction of calculating the difference in time and take midnight into consideration?

TIA

Have A Great Day!!!,

Nathan
Senior Test Lead

 

[jsteph]

If you use DAteDiff, use:
datediff("n",Some date/Time,Some other Date/Time )

This gives actual minutes, regardless of days, (you can go seconds if you need that level of detail), and now you can divide to get hours, days, years, etc.
--jsteph

 

[scriverb]

Well when converting elapsed time to a decimal so that they can be summed requires a little manipulation. I have indicated a few threads that have already addressed this process in a number of ways. I am also displaying a little bit of VBA code that I wrote to perform this function. It includes the date process but if there is only time involved in the field it has no consequence. Just use what you need to meet your needs. The code could be put in a Function and called either in a control or a query. This will work over midnight.

Dim vDays As Integer
Dim vHours As Integer
Dim vMinutes As Integer
Dim vTime As Double
Dim vTotalMinutes As Long
vTotalMinutes = DateDiff("n", CheckIN, CheckOut)
vDays = CInt(DateDiff("n", CheckIN, CheckOut) / 1440)
vHours = (CInt(DateDiff("n", CheckIN, CheckOut)) - (vDays * 1440)) / 60
vMinutes = (CInt(DateDiff("n", CheckIN, CheckOut)) - (vDays * 1440)) - (vHours * 60)
vTime = vHours + (vMinutes / 60)
MsgBox "Total Minutes: " & vTotalMinutes & vbCrLf & "Days: " & vDays & " Hours: " & vHours & " Minutes: " & vMinutes & vbCrLf & vbCrLf & "Elapsed time: " & vTime & " days"

Threads:
Thread705-542015
Thread181-532313
Thread181-529272

Let me know if this works for you.




Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[nat1967]

Hi bob,

Thanks for the code and the links. I finally found what I needed in one of your links.

I forgot to combine my date and time fields. DOH!!! no wonder nothing was working.

here is what I ended up with. Demonstrated with your code:

Dim vDays As Integer
Dim vHours As Integer
Dim vMinutes As Integer
Dim vTime As Double
Dim vTotalMinutes As Long


vTotalMinutes = DateDiff("n", DateIn + TimeIn, DateOut + TimeOut)
vDays = CInt(DateDiff("n", DateIn + TimeIn, DateOut + TimeOut) / 1440)
vHours = (CInt(DateDiff("n", DateIn + TimeIn, DateOut + TimeOut)) - (vDays * 1440)) / 60
vMinutes = (CInt(DateDiff("n", DateIn + TimeIn, DateOut + TimeOut)) - (vDays * 1440)) - (vHours * 60)
vTime = vHours + (vMinutes / 60)
MsgBox "Total Minutes: " & vTotalMinutes & vbCrLf & "Days: " & vDays & " Hours: " & vHours & " Minutes: " & vMinutes & vbCrLf & vbCrLf & "Elapsed time: " & vTime & " days"


Thanks again!!!

Have a star!!!

Have A Great Day!!!,

Nathan
Senior Test Lead