I'm not a guru in Excel and couldn't find the MROUND formula you mentioned.
Did you create it yourself? You can probably use the same logic of that
formula within InfoPath to round the minutes. By the way, what do you mean
with round to the quarter hour? If a user types in 11:35, it rounds it to
11:30 or 11:45? Is that what you meant? If not, can you give me a few
examples?
---
S.Y.M. Wong-A-Ton
> Thanks. There is no reason I wanted to use code other than to maybe pick up
> on some basics. I was always under the impression that code was more
[quoted text clipped - 51 lines]
> > > > >
> > > > > Thanks.
I'm no guru in Excel either. I found the function on the Internet.
Basically it takes a cell in which time is entered and gives the time back,
rounded to the nearesr quarter-hour. For example if a user enters 11:07, it
would give you back 11:00. If a user enters 11:08, it would give you back
11:15. It allows you to write a formula and it would function as a time
clock. Most of our users feel the need to enter the exact time the are in
and out. But, the accounting department need to know the time they worked to
the quarter-hour. We had a time clock at one point and could not afford the
service. So, I gathered my powers of Internet search and wrote the lengthy
formula that rounds BOTH times (in and out) separately, calculates the
difference, and returns the total (in a decimal format). None of which is
code, all formula; if's, vlookup's, lots of brackets, etc., etc. Although I
am more than confident in my powers of deduction and logical reasoning, I
have 0 powers in coding and therefore none of it is logical to me.
While I appreciate your attempts to make things more lucid, I certainly
understand that you need to make a livin and can only offer limited support
in this "free" forum. Please be mindful that I work as the sole IT support
for a small non-profit that cannot spend money to research a product that was
given to us in-kind by MS. This is just my attempt to make things easier on
employees so they can spen more time investing in our mission, thus bringing
down costs and elevating productivity.
Thank you bunches for all of your wonderful insight.
Richard
> I'm not a guru in Excel and couldn't find the MROUND formula you mentioned.
> Did you create it yourself? You can probably use the same logic of that
[quoted text clipped - 60 lines]
> > > > > >
> > > > > > Thanks.
S.Y.M. Wong-A-Ton - 22 Nov 2006 06:13 GMT
I've studied your scenario a bit more, and while you can use rules to
re-format the time using a technique as described in this article
http://enterprise-solutions.swits.net/infopath2003/article.php?t=auto-format-tim
e-on-lostfocus&c=infopath2003
, it is virtually impossible to do this for your situation, since the fields
are within a repeating table. So my conclusion is that you will have to use
code to re-format the time. The best place to do this is in the OnAfterChange
event of a time field and using logic that takes the time apart and then
checks and changes the minutes based on criteria, e.g. if the amount of
minutes is less than 7 then re-format time field to the hour, if the amount
is between 7 and 22, then re-format to the quarter hour etc.
---
S.Y.M. Wong-A-Ton
> I'm no guru in Excel either. I found the function on the Internet.
> Basically it takes a cell in which time is entered and gives the time back,
[quoted text clipped - 86 lines]
> > > > > > >
> > > > > > > Thanks.