Rounding Time to 1/4 hour in decimel format.

[unclefink]

I have two cells called Start Date/Time and End Date/Time and TotalTime, qry_StoreReportStart Date/TimeEnd Date/TimeTotalTime4/11/2012 10:00:00 AM4/11/2012 1:46:00 PM3.77

I am in need of help to make the "Total Time" round to the next quarter hour and not round on the hour. Example: The 3.77 TotalTime example above would round to 3.75

10:06 would round back to 10:00 and 10:08 would round up to 10:15. The intention of my result is to somewhat match up to our time clock system which totals in 1/4 hour increments which translates 10:15 to 10.25.

Hopefully this makes since. I've seen a lot of examples illistrated in Excel so hopefully its also possible in access in a reasonable understanding process.

 

[apr pillai]

The time difference between the two sample date/time calculated in days terms equals to 0.156944444444444.

This must be converted into Seconds and then calculate in hours and minutes with the following expression:

((EndDate/Time - StartDate/time)*86400)/3600 will give you the result = 3.77

This can be rounded using the Excel MRound() Function as given below:

=MRound(((EndDate/Time - StartDate/time)*86400)/3600,0.25)

Result: 3.75.

MS-Access doesn't have this Function. I have written it in Access with the same name MRound(). You can get the code of this function from the link given below:

Rounding Function MRound of Excel

 

[unclefink]

I just spoke with the person who i'm doing this db for and came up with the necessary rounding times.


In reading my origional post, I realized I didnt note that my intention was to go both ways depending on the time the individual started/stopped a job.

In looking at the overall picture, my guess is that it might be easier to round the start and end time then calculate the difference to come up with my 1/4 hour total time.


Which brings me to my predicament, im not sure how to do that.

Here is the layout in which i need the times to round accordingly, for sample purposes, i'll use a 2pm hour.


1:53-2:07 rounds to 2:00 pm
2:08-2:22 rounds to 2:15 pm
2:23-2:37 rounds to 2:30 pm
2:38-2:52 rounds to 2:45 pm

I then need the result time to read 2.00, 2.25, 2.50, or 2.75.


Hopefully this makes sense.

 

[unclefink]

Eureka, after massive searching, I think i've found it.

TotalTime: Round((([End Date/Time]-[Start Date/Time])*24)*4,0)/4

Thanks for the help, its greatly appreciated.