?
Solved

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

Posted on 2012-04-05
12
Medium Priority
?
308 Views
Last Modified: 2012-04-10
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
Comment
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
  • Learn & ask questions
12 Comments
 
LVL 48

Expert Comment

by:Dale Fye
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 48

Accepted Solution

by:
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
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 

Author Comment

by:rayscue
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

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

Author Comment

by:rayscue
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)))

thanks for your time.
0
 
LVL 34

Assisted Solution

by:Norie
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

by:Dale Fye
ID: 37812720
Rayscue,

Don't forget to close this out.
0
 

Author Comment

by:rayscue
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.  I resolved it myself, therefore I am closing the question.
0
 
LVL 48

Expert Comment

by:Dale Fye
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

by:rayscue
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

by:Dale Fye
ID: 37827633
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.
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
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 …
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Suggested Courses
Course of the Month10 days, 16 hours left to enroll

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question