Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / General MS InfoPath Questions / April 2006

Tip: Looking for answers? Try searching our database.

Date Diff using rules

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
lizzybizzy - 10 Apr 2006 04:26 GMT
I found the example on calculating the date difference in InfoPath without
code very helpful!  Thank you Ms. Wong A Ton for being the only person who
has this posted!!!  My only problem I have with this is that the code doesn't
account for work hours.  I am trying to create a leave request form and when
I test for one day of leave it calculates this as 9hrs instead of 8hrs.  Any
suggestions on how I could manipulate this code to account for work hours?  
Have you had anyone ask about this before?  I appreciate any help on this.
dixieee - 17 Apr 2006 10:14 GMT
could u post the code plz?
lizzybizzy - 17 Apr 2006 14:56 GMT
> could u post the code plz?

This is where I found the code for this date diff calculation.  Although
it's not really code, this example is using rules and actions in InfoPath:

http://enterprise-solutions.swits.net/infopath/time-calculations-using-rules.htm

Thanks for the help!
S.Y.M. Wong-A-Ton - 19 Apr 2006 11:06 GMT
Hi. Sorry for the delay.

I'm not sure I understand your scenario well enough to answer your question.
The example on my website has been kept very simple in that it only
calculates the amount of hours between two given times on one day. I don't
quite understand what you mean with "when I test for one day of leave it
calculates this as 9hrs instead of 8hrs". And what are "work hours" for you?
Is that a set period every day? E.g. from 09:00 - 17:00? Or do you want to
subtract 8 hours from the time difference calculation in each row? Perhaps if
you elaborate a bit, I might be able to help.
---
S.Y.M. Wong-A-Ton

> I found the example on calculating the date difference in InfoPath without
> code very helpful!  Thank you Ms. Wong A Ton for being the only person who
[quoted text clipped - 3 lines]
> suggestions on how I could manipulate this code to account for work hours?  
> Have you had anyone ask about this before?  I appreciate any help on this.
lizzybizzy - 19 Apr 2006 15:25 GMT
I do want to calculate the amount of hours between two given times on one
day.  The work hours that I need to calculate on are 8:30 - 17:30 and when
calculated this should represent 8 hours of leave since you do not take into
account your lunch hour as leave time.  Where I work we earn hours for leave
and that is how we calculate our leave time.  1 work day = 8hrs of leave.

> Hi. Sorry for the delay.
>
[quoted text clipped - 16 lines]
> > suggestions on how I could manipulate this code to account for work hours?  
> > Have you had anyone ask about this before?  I appreciate any help on this.
S.Y.M. Wong-A-Ton - 20 Apr 2006 08:30 GMT
It sounds like you just want to subtract 1 hour from the daily total to
account for lunch. Am I close? If so, there are two possibilities. If lunch
doesn't always last an hour, you could add an extra field where users can
enter the amount of minutes they spent having lunch and then subtract this
amount from the total when performing the calculation. If lunch always lasts
an hour, you could just modify the formula to subtract 1 hour (=3600
seconds). You could change the formula for the rule on the startTime to
something like

(((number(substring(../my:endTime, 1, 2)) * 3600) +
(number(substring(../my:endTime, 4, 2)) * 60)) - ((number(substring(., 1, 2))
* 3600) + (number(substring(., 4, 2)) * 60)) - lunchTime) div 3600

where you should replace lunchTime with the value 3600 or with the field
containing the amount of minutes for lunch multiplied by 60, so use something
like

(((number(substring(../my:endTime, 1, 2)) * 3600) +
(number(substring(../my:endTime, 4, 2)) * 60)) - ((number(substring(., 1, 2))
* 3600) + (number(substring(., 4, 2)) * 60)) - 3600) div 3600

for the static 1-hour lunch case, or

(((number(substring(../my:endTime, 1, 2)) * 3600) +
(number(substring(../my:endTime, 4, 2)) * 60)) - ((number(substring(., 1, 2))
* 3600) + (number(substring(., 4, 2)) * 60)) -
(number(../my:lunchTimeInMinutes * 60))) div 3600

for the dynamic amount of minutes for lunch entered by the user.
---
S.Y.M. Wong-A-Ton

> I do want to calculate the amount of hours between two given times on one
> day.  The work hours that I need to calculate on are 8:30 - 17:30 and when
[quoted text clipped - 22 lines]
> > > suggestions on how I could manipulate this code to account for work hours?  
> > > Have you had anyone ask about this before?  I appreciate any help on this.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.