How do I round off time (1 Viewer)

[ncimike3]

I've searched the forum but have found difficulty in solving this problem. I am trying to round off times to the nearest half hour. To be clear, I don't want to only round down or only round up. I need the rounding to be to the nearest half hour. I want to do this in the query, not vba. I've attached a picture of the query. Thanks in advance for any help.

 

[plog]

I want to do this in the query, not vba.

How about in a query using VBA? Because that's the most efficient way I see to do this. What you would do is create a function in a module, do your logic, then return the value you want.

However, before we get to that, what happens at 11:55 PM? The query is going to return 12:00 AM, but does that now make the date wrong? Is the field 'Date' (which is a poor choice for a field name because its a reserved word http://support.microsoft.com/kb/286335) go together with the field 'TimeIn'? If so, you really should merge them -- its called a Date/Time field for a reason.

 

[ncimike3]

I actually found a solution. I worked on this for a while and came up with this:

AdjTimeIn: TimeSerial(Int(Int((Hour([TimeIn])+((Minute([TimeIn])+15)/60))*2)/2),((Int((Hour([TimeIn])+((Minute([TimeIn])+15)/60))*2)/2)-Int(Int((Hour([TimeIn])+((Minute([TimeIn])+15)/60))*2)/2))*60,0)

attached is a screen shot of the result

 

[plog]

Yeah, that should be a vba function. What about my questions about the date?