Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

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

Posted on 2012-04-05
Medium Priority
311 Views
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.
0
Question by:rayscue
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 48

Expert Comment

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

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

LVL 17

Expert Comment

ID: 37811968
0

LVL 48

Accepted Solution

Dale Fye earned 750 total points
ID: 37811983
If they are actually stored as strings, you cannot store "0745" as a number (but you can as a date/time), then I would try:

intMinutes = DateDiff(TimeValue(Left([Time1],2), Mid([Time1],3),0), TimeValue(Left([Time2],2), Mid([Time2], 3),0))
0

Author Comment

ID: 37812077
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
0

LVL 32

Expert Comment

ID: 37812174
What are the datatypes of appt_time and change_time?
0

Author Comment

ID: 37812177
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)))

0

LVL 35

Assisted Solution

Norie earned 750 total points
ID: 37812575
If the field is numeric this will work.

DateDiff('n',TimeValue(Format([appt_time],"00:00")),TimeValue(Format([change_time],"00:00")))

Oops, just noticed you had a solution.
0

LVL 48

Expert Comment

ID: 37812720
Rayscue,

Don't forget to close this out.
0

Author Comment

ID: 37827385
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. &nbsp;I resolved it myself, therefore I am closing the question.
0

LVL 48

Expert Comment

ID: 37827386
"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?
0

Author Comment

ID: 37827605
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.
0

LVL 48

Expert Comment

ID: 37827633
No problem. go ahead and close it.

"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.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+) Â  Â  as used in Oracle; Â  Â  *= Â  Â  =* Â  Â as used in Sybase â€¦
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server.Â RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at: Â  htâ€¦
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastrâ€¦
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper cornâ€¦
###### Suggested Courses
Course of the Month12 days, left to enroll