Time Rounding

[Anuviel]

I just finished Employee and Time Cards db for the company where I work. Everything is working nicely and no problems throughout, thanks to people that helped here. However the db calculates times correctly by the minute. I was told to remake it so that it rounds up the times - cannot figure that one out. Please help - it should round up like this:

I will use 1:00 as a full hour example, the rounding depends on the minutes

Between 1:00 & 1:07 - IN time will be 1:00

Between 1:08 & 1:15 - IN time will be 1:15

Between 1:16 & 1:22 - IN time will be 1:15

Between 1:23 & 1:30 - IN time will be 1:30

Between 1:31 & 1:37 - IN time will be 1:30

Between 1:38 & 1:45 - IN time will be 1:45

Between 1:46 & 1 52 - IN time will be 1:45

Between 1:53 & 2:00 - IN time will be 2:00

That is how he put it for me, now how I understand it, that is the same as this, but it is not according to him:

Between 1:00 & 1:07 - IN time will be 1:00

Between 1:08 & 1:22 - IN time will be 1:15

Between 1:23 & 1:37 - IN time will be 1:30

Between 1:38 & 1:52 - IN time will be 1:45

Between 1:53 & 2:00 - IN time will be 2:00

So basically rounding every 15 minutes. The way he does it when writing total hours is:

.25 = 15 minutes

.50 = 30 minutes

.75 = 45 minutes

1 = 1 hour

Basically we have a time clock which punches in, in HH:MM format so what I need is when I enter:

10:06 in the IN field, I need the filed to round it up to 10:00 or if I enter 15:23 I need it rounded to 15:30.

Right now I am using an auto enter calc with Replace ( Right ( "0000" & Out ; 4 ) ; 3 ; 0 ; ":" ) so that I can simply enter 1006 and have it changed to 10:06 - helps with the typing.

The more I look at this problem the more complicated it seems - why cannot he simply use exact times which work beautifully.

Any info, comments and suggestions appreciated and welcome.

If I cannot get my head around this in two days I'll just have to drop it I guess, maybe a custom function or something similar?

[Lee Smith]

I would use the Round Function.

Check out the file Here and see if it is what you need. Click on Resource and search for Round Time.

HTH

Lee

Edited September 25, 2007 by Guest
forgot the second part.

[Anuviel]

Perfect, that will work, thank you so much. One more quick question how would I combine

Round ( In / 900 ; 0 ) * 900 and Replace ( Right ( "0000" & Out ; 4 ) ; 3 ; 0 ; ":" ) into one calculation? - not sure on syntax.

Thank you.

[Anuviel]

Well tried putting a field over a field, such as into one I enter the exact time and the field that is over that field (note selectable in find or browse) will round the time from the first field but that did not work as they are in a portal and it caused the portal to be filled with same entry in all of the rows? When I tried putting the above calculation together I constantly keep getting 0:15 - my syntax is most likely wrong....

[Lee Smith]

Huh?

[Anuviel]

Thanks, well the file you pointed me to solved my rounding problem and all is fine in that respect, thank you.

It however raised another problem:

My time field is formated to display time as hh:mm - formated via layout time format option.

In the field options I have an auto-enter calculation Replace ( Right ( "0000" & Out ; 4 ) ; 3 ; 0 ; ":" ) which allows me to type 1006 into the field which turns to 10:06 after I hit enter.

The solution for the rounding that you pointed me to is: Round ( In / 900 ; 0 ) * 900

In order to round I have replaced auto-enter calculation from Replace ( Right ( "0000" & Out ; 4 ) ; 3 ; 0 ; ":" ) to Round ( In / 900 ; 0 ) * 900.

Now if I type 1006 - it will not format the time correctly as I removed Replace calculation. I have to type 10:06.

It may seem marginal however I am not the one that will be entering the times in the db and my boss is week when it comes to typing so he much prefers to type 1006 instead of 10:06 which, he says saves him time during data entry.

I am wondering how I can achieve to be able to type 1006 into the field and it being formated as 10:06 and then rounded.

Hope this clarifies it, I am lost myself.

Thank you kindly....

[comment]

Try:

Let (

entry = GetAsTime ( Replace ( Right ( "0000" & TimeField ; 4 ) ; 3 ; 0 ; ":" ) )

;

Round ( entry / 900 ; 0 ) * 900

)

[Anuviel]

Tried is and if I enter 1006 I get 1006:00. Just to make sure.

I copied your CF and just changed TimeField to my time field name and in the auto-enter calculation I just inserted the CF - is there anything else I ned to make or is that it?

[comment]

I don't know - it works for me here. It's not a custom function, BTW.

[Anuviel]

Thanks, it works now, my mistake, messed up, saw let function and for some reason thought of CF - corrected that and it works perfectly, thank you so much. :(