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.
rayscueAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:
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
Dale FyeCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

rayscueAuthor Commented:
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
awking00Commented:
What are the datatypes of appt_time and change_time?
0
rayscueAuthor Commented:
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
NorieVBA ExpertCommented:
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
Dale FyeCommented:
Rayscue,

Don't forget to close this out.
0
rayscueAuthor Commented:
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
Dale FyeCommented:
"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
rayscueAuthor Commented:
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
Dale FyeCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.