Rounding time to the previous half hour

[AshikHusein]

A simple solution to this seems to be eluding me.

I have time fields in a table. I want to create another column which rounds of the time to the previous half hour.

For example if it is 7:25 AM then that should map on to 7:00 AM

On the other hand if it is 7:45 AM then it should map on to 7:30 AM.

Would appreciate an insight into this.

At the moment I am using DatePart function and things of that sort which literlly ends up converting the time to a number.

 

[WayneRyan]

Ashik,

Just a quick try at it.

Indented for readability:

MyTime = IIf(Right(Format(MyTime, "hh:mm"), 2) = "00" Or 
             Right(Format(MyTime, "hh:mm"), 2) = "30", 
                MyTime, 
                IIf(Right(Format(MyTime, "hh:mm"), 2) < "30", 
                    Left(Format(MyTime, "hh:mm"), 3) & "00", 
                    Left(Format(MyTime, "hh:mm"), 3) & "30"))

Wayne