Round the time to Integer values

[krkow]

HI All,

I would like to Round the time what I have bellow to integer Values (10 min peridos ) for example

15:43:14 -> 15:40:00.

May I pefrom this by creating Query or Table is enough?

Thank a lot

[alansidman]

Allen Browne has a UDF that can be found here

http://allenbrowne.com/round.html#RoundTime

Alan

[krkow]

HI

Let me explain a little bit more advance this issue.

I would like to create a query which compares the time (hh:mm:ss, but seconds always must be "0" and minutes roundes to 10 min periods per hour so eg. 12:10:00) between two tables.

At one of both tables has integer 15:00:00 of time, but scond table has 15:04:16- so before that the time must be rouned

I'm created a query and saw an error #FUNC! in Data sheet overview.

Do you know how Can I perform that to achieve the data according to time from neighberhood table (table with not integer time )?

[krkow]

this same what above.

Thanks a lot for advance!

[alansidman]

Did you try Allen Brown's solution?

Alan

[krkow]

Yes,

I have tried to used Allan Brown's solution, but I need more advice regardiing this issue:

I have the date and time (hh:mm:ss) and I would like to get in my query integer values of minutes . It means eg 15:43:14 then I would like to get 15:40:00 in query or table.

So, Does anyone know how to do this?

I have tried by using =round() in builder functions of my query, but it doesn'r work.

Thanks

Krystian

[ssanfu]

I have tried by using =round() in builder functions of my query, but it doesn'r work.

Thanks

Krystian

A simpler formula (than Allen Browne's) for your query is:

Round(FieldName * 144, 0) / 144

Change "FieldName" to the name of your time field

[krkow]

HI,

Thanks for the formula to my query. After applied below function :

Round(FieldName * 144, 0) / 144

I got the result as a Number.

2011-05-10 09:05:27 -> 40673,3819444444.

Do you know how can I change the format for this. I have tried format settigns of my query, but I don't see the "date/time" option?

[ssanfu]

I got the result as a Number.

2011-05-10 09:05:27 -> 40673,3819444444.

Do you know how can I change the format for this. I have tried format settigns of my query, but I don't see the "date/time" option?

I wouldn't worry about it in a query, since you can set the format when it is bound to a control on a form. But, there are a couple of ways to set the format for a column.
In a column, you can use the format function:

 TimeRounded:Format([FieldName],"hh:nn:ss")

Change "FieldName" to your field name.

Another way is, in design view, right click in the column and select properties. In the format row, enter hh:nn:ss .

[krkow]

HI

I have set the format according with yours proposal. It means right click on collumn and following to propeties and put "hh:nn:ss" for Format field.

As I result I succeed the 10 min intervals of required time.

Thanks a lot for support!

krkow