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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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 FyeOwner, Developing Solutions LLCCommented:
The normal method, if these are actually stored as date/time values would be to use the DateDiff function:

intMinutes = DateDiff("n", [Time1], [Time2])
Dale FyeOwner, Developing Solutions LLCCommented:
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))

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
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

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
awking00Information Technology SpecialistCommented:
What are the datatypes of appt_time and change_time?
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.
NorieAnalyst Assistant Commented:
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.
Dale FyeOwner, Developing Solutions LLCCommented:
Rayscue,

Don't forget to close this out.
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.
Dale FyeOwner, Developing Solutions LLCCommented:
"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?
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.
Dale FyeOwner, Developing Solutions LLCCommented:
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.
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.