asked on

Need MS Access logic to calculate difference between two times stored as a number 0000 to 2400

I need to compute the difference between two time fields stored as numbers in military time. I have tried just subtracting them, and it works for most times, but when I try to get the difference between say 0745 and 0808, it gives me 63 minutes instead of 23 minutes because of the 10 base instead of 60 base logic. I have tried multiple functions including the Datediff function using the 'n' data type, but I can't get it to work correctly. Any help would be appreciated.

Dale Fye

The normal method, if these are actually stored as date/time values would be to use the DateDiff function:

intMinutes = DateDiff("n", [Time1], [Time2])

Anuroopsundd

http://www.everythingaccess.com/tutorials.asp?ID=Calculating-Elapsed-Time

ASKER CERTIFIED SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rayscue

ASKER

Here are the results for the two ways I've tried it...

appt_time change_time DateDiff("n",appt_time,change_time) appt_time - change_time
830        1453        897120        -623
700        1041        491040        -341
730        1051        462240        -321
745        808        90720        -63

awking00

What are the datatypes of appt_time and change_time?

rayscue

ASKER

I resolved the issue by converting the number fields to a string format and then doing the datediff function.

DateDiff('n', (Left(appt_time,Len(appt_time)-2) & ":" & Right(appt_time,2)) , (Left(change_time,Len(change_time)-2) & ":" & Right(change_time,2)))

thanks for your time.

SOLUTION

Norie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dale Fye

Rayscue,

Don't forget to close this out.

rayscue

ASKER

I've requested that this question be closed as follows:

Accepted answer: 0 points for rayscue's comment #37812177

for the following reason:

This was the best way to solve the problem.  I resolved it myself, therefore I am closing the question.

Dale Fye

"I resolved it myself"

Really, your solution looks a lot like what both imnorie and I posted, with the exception that you had to first convert the numbers into text to get it to work. Did you really have the idea to use datediff() function call on your own, or was that because of input received by experts?

rayscue

ASKER

Fyed,

Please note in my original posted question, I stated, "I have tried multiple functions including the Datediff function using the 'n' data type, but I can't get it to work correctly." So the answer to your question of whether I had the idea to use the datediff() function call on my own, is "yes". I obviously was already using that function before I posted the question to the "experts". I just continued to work on the logic in the Datediff function so that it worked correctly.

However, if either of you need the points, I will share them between the two of you. But as you can see, I was already doing what you suggested in your posts, I just had to convert the numbers to strings as I stated before.

Dale Fye

No problem. go ahead and close it.

The confusing part, and the reason you didn't get an acceptable answer initially was this comment:

"stored as numbers in military time"

and the fact that you did not provide any example data. Still cannot figure out why you would want to store these time values as integers rather than date/time values.