in timesheet subtract finish time from start time for hours worke.

G

Geoff May

I am developing a timesheet and have a cell with start time and a cell with
finish time and a third cell that subtracts finish time from start time to
give hours worked for the day. I have tried to use formulas in the third cell
and rules but can't get the result in time format. I can do easily in Excel
but not in INfoPath. Appreciate your advice Thanks Geoff May
 
S

S.Y.M. Wong-A-Ton

Hi Geoff,

Try doing a search on "Adding Time????" in this newsgroup. There is a post
there that gives the solution.

Hope this helps.

Regards,
S.Y.M. Wong-A-Ton
 
G

Geoff May

Hi S.Y.M.Wong-A_TON, Unfortunately I am still having problems. If I use the
simple formula of A2-A1 I get 'NaN' in the third cell. If I use the larger
formula supplied by bpeltzer I get an error message "Node- Test Expected. Any
other ideas? Thanks Geoff
 
S

S.Y.M. Wong-A-Ton

Not sure how they got either one of those two solutions to work... sorry. Is
updating the third cell from code (JScript or C#) an option for you?

If it is, here is what you could do: After retrieving and parsing the times
(and dates if the time can go over to another day), you can use script code
to calculate the elapsed hours and minutes. Here I've used Jan 1, 1970 at
7:00 as the start date/time and Jan 2, 1970 at 15:30 as the end date/time.

---
var dtBegin = new Date(1970,1,1,7,0,0);
var dtEnd = new Date(1970,1,2,15,30,0);

var elapsed = (dtEnd.getTime() - dtBegin.getTime()) / 1000;

var elapsedHours = (elapsed - (elapsed % 3600)) / 3600;
var elapsedMin = (elapsed % 3600) / 60;

var elapsedHoursWithDecimals = elapsed / 3600;
 
S

S.Y.M. Wong-A-Ton

I forgot to mention yesterday that I checked the calculations that were
mentioned in the post. It makes no sense to subtract 07:00 (A1) from 15:30
(A2), since this will always result in something that is not a number, which
is why you are getting NaN back as a value for the third cell. Changing the
type of the third cell to a numeric data type will not fix this problem.

The second longer function contains functions that are not present in
InfoPath when putting together a formula, which is why you are getting back
an error.

I'm afraid that the only way to solve this problem is through code. Since
this is a question that has popped up often, I will try to compose a proper
solution and post it this weekend.
 
G

Geoff May

S.Y.M. Wong-A-Ton Many thanks. If the result includes using code can you also
supply advice on how to use code in Info path. Regards Geoff
 
G

Geoff May

Hi S.Y.M. Wong-A-Ton, I could follow your instrauctions to step 11 ok. I was
not confident adding the extra line of code but did what you said. my code
for start and end time now looks like:
/*
* This file contains functions for data validation and form-level events.
* Because the functions are referenced in the form definition (.xsf) file,
* it is recommended that you do not modify the name of the function,
* or the name and number of arguments.
*
*/

// The following line is created by Microsoft Office InfoPath to define the
prefixes
// for all the known namespaces in the main XML data file.
// Any modification to the form files made outside of InfoPath
// will not be automatically updated.
//<namespacesDefinition>
XDocument.DOM.setProperty("SelectionNamespaces",
'xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2006-01-24T23:21:32"');
//</namespacesDefinition>


//=======
// The following function handler is created by Microsoft Office InfoPath.
// Do not modify the name of the function, or the name and number of
arguments.
// This function is associated with the following field or group (XPath):
/my:myFields/my:timeEntries/my:timeEntry/my:startTime
// Note: Information in this comment is not updated after the function
handler is created.
//=======
function msoxd_my_startTime::OnAfterChange(eventObj)
{
// Write code here to restore the global state.

if (eventObj.IsUndoRedo)
{
// An undo or redo operation has occurred and the DOM is read-only.
return;
}
updateFields(eventObj.Source);
}
// A field change has occurred and the DOM is writable. Write code here to
respond to the changes.

}

//=======
// The following function handler is created by Microsoft Office InfoPath.
// Do not modify the name of the function, or the name and number of
arguments.
// This function is associated with the following field or group (XPath):
/my:myFields/my:timeEntries/my:timeEntry/my:endTime
// Note: Information in this comment is not updated after the function
handler is created.
//=======
function msoxd_my_endTime::OnAfterChange(eventObj)
{
// Write code here to restore the global state.

if (eventObj.IsUndoRedo)
{
// An undo or redo operation has occurred and the DOM is read-only.
return;
}
updateFields(eventObj.Source);
}


// A field change has occurred and the DOM is writable. Write code here to
respond to the changes.

}
If I try to Preview the Form at this stage I get the following error message:
nfoPath cannot open the selected form because of an error in the form's code.
The following error occurred:

Syntax error
File:script.js
Line:37
At step 16 I am also confused. I could not down load the JScript File. It
would not open. Do I just copy the code you wrote in step 16. If so then
where do I copy it to? Does it go into totalHours? Sorry to keep hounding you
but I am out of my league when we get into code. Thanks Geoff
 
S

S.Y.M. Wong-A-Ton

If I try to Preview the Form at this stage I get the following error message:
nfoPath cannot open the selected form because of an error in the form's code.
The following error occurred:

Syntax error
File:script.js
Line:37

I see a closing curly bracket ( } ) too many in your code, which might be
causing the script error. :) You had to only add
updateFields(eventObj.Source); to the OnAfterChange event.
At step 16 I am also confused. I could not down load the JScript File. It
would not open.

I zipped up the JScript file. If you have WINZIP or are working on WinXP,
you should be able to open it.
Do I just copy the code you wrote in step 16. If so then
where do I copy it to? Does it go into totalHours?

Yes. Paste it just below the last closing curly bracket ( } ) in your code.
And no, it does not go into totalHours. Actually, you do not have to create
an OnAfterChange event for totalHours.
Sorry to keep hounding you
but I am out of my league when we get into code.

That's okay. Here's what you can do: Since I don't publish my email
addresses (due to spamming), you can Google my name, click on the first link
that appears, go to the "Contact" page on that site, and send me a message
from there. I'll try and help you get the form working or send you a working
sample.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top