Solved

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

Posted on 2012-04-05
12
299 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
12 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
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

by:Anuroopsundd
ID: 37811968
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 250 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

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 31

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 33

Assisted Solution

by:Norie
Norie earned 250 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 47

Expert Comment

by:Dale Fye (Access MVP)
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 47

Expert Comment

by:Dale Fye (Access MVP)
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 47

Expert Comment

by:Dale Fye (Access MVP)
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This video discusses moving either the default database or any database to a new volume.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

706 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now