Anuviel Posted September 25, 2007 Share Posted September 25, 2007 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? Link to comment Share on other sites More sharing options...
Lee Smith Posted September 25, 2007 Share Posted September 25, 2007 (edited) 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. Link to comment Share on other sites More sharing options...
Anuviel Posted September 25, 2007 Author Share Posted September 25, 2007 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. Link to comment Share on other sites More sharing options...
Anuviel Posted September 25, 2007 Author Share Posted September 25, 2007 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.... Link to comment Share on other sites More sharing options...
Lee Smith Posted September 26, 2007 Share Posted September 26, 2007 Huh? Link to comment Share on other sites More sharing options...
Anuviel Posted September 26, 2007 Author Share Posted September 26, 2007 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.... Link to comment Share on other sites More sharing options...
comment Posted September 26, 2007 Share Posted September 26, 2007 Try: Let ( entry = GetAsTime ( Replace ( Right ( "0000" & TimeField ; 4 ) ; 3 ; 0 ; ":" ) ) ; Round ( entry / 900 ; 0 ) * 900 ) Link to comment Share on other sites More sharing options...
Anuviel Posted September 26, 2007 Author Share Posted September 26, 2007 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? Link to comment Share on other sites More sharing options...
comment Posted September 26, 2007 Share Posted September 26, 2007 I don't know - it works for me here. It's not a custom function, BTW. Link to comment Share on other sites More sharing options...
Anuviel Posted September 26, 2007 Author Share Posted September 26, 2007 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. :( Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 6191 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now