Time to number

[Timoty]

Hello to all.

I have been at this for 6 hours and have read countless entires in this forum. I have come close......but not close enough.

I am trying to calculate the difference between two times. I can't use a combination of date and time because if I do, those who use the database won't use it. To much info to input. Right now they input a simple time in the form 16:43 to represent 4:43 PM. They are used to, and like that method.

Everything works fine until I span midnight.
Now I have read all the postings about this issue and how datediff can be used with some wacked out formulas.

I can get the proper time by simply using the formula Format([timestart]-1-[Timeend],"Short Time") in a query. However, I need the total time in hours and minutes as a decimal.

Ex. 7:30 would become 7.5
To make things more complicated I need it rounded to the closest 15 minutes.

Ex. 7:35 would become 7.5
7:40 would become 7.75

If there is a way to do this with datediff great!

Now as I mentioned I have already solved the midnight span problem with the formula above but the result is in the form 7:30.

So if someone can just tell me how to get my query field "TIME" from 7:30 to another field TIMENUM as 7.5 even better

If someone can tell me how to get field TIMESTART and TIMEEND to give me a decimal number EVEN WHEN THE TIMES SPAN MIDNIGHT....well then you are a genius.

Take that one step further to the closest 15 minute interval and you are a genius and my hero =-)

 

[ScottGem]

Sure I can, record the date also. Sorry if that doesn't fit your needs but that's what you do need.

My question to you is when are the times entered? If they are always entered thje same day they are recorded, then just capture the date behind the scenes and your problem is solved. If the times are entered on different days, use a date/time picker control so they don't have to type much.

 

[Jon K]

I need the total time in hours and minutes as a decimal.

Ex. 7:30 would become 7.5
To make things more complicated I need it rounded to the closest 15 minutes.

Ex. 7:35 would become 7.5
7:40 would become 7.75

TimeNum: DatePart("h",[timestart]-1-[Timeend])+CInt(DatePart("n",[timestart]-1-[Timeend])/15)/4


Note
When rounded to the closest 15 minutes, 0:01 to 0:07 would become 0 (hours).
.

 

[Timoty]

Will give it a go.

I have the database at work....thankfully!

I will give it a try on Monday and post my success/failure.

If it works...well you'll beat my highest expectation =-)

 

[Timoty]

Forgot. I emailed the database to myself.

Tried it...it worked.

I BOW BEFORE YOU Jon K.

Seriously though. I should have just asked after 2 hours but I don't want to abuse this forum. I try to do it myself first because that is the way to learn. But wasting a day isn't really productive.

I really appreciate it. I noticed your name attached to other threads realated to similar problems. I hope this thread can help others.

As a last note; if I want to dispence with the rounding to 15 minutes do I just remove the end of the expression like below?

DatePart("h",[timestart]-1-[Timeend])+CInt(DatePart("n",[timestart]-1-[Timeend])

Thanks

 

[Jon K]

If you don't want to round it to the nearest 15 minutes, you don't need the CInt() function:-

DatePart("h",[timestart]-1-[Timeend]) + DatePart("n",[timestart]-1-[Timeend])/60

It will return the time in hours with several places of decimal.


If your version of Access has the Round() function, you can round it to 2 places of decimal like this:-

Round(DatePart("h",[timestart]-1-[Timeend]) + DatePart("n",[timestart]-1-[Timeend])/60, 2)
.

 

[Timoty]

Many Thanks

Many Thanks Jon K.

I certainly appreciate the time you took to give me the answers.
I have had more trouble with times and dates than anything else I have done. After reading the threads I understand why. Funny thing is some people were recommending 4 pages of code to get around the issue and I new there had to be a simpler way.

sure enough...there was.