Calculate Time

[boyemillar]

I have created a DB in MSSQL and a couple of forms in Infopath 2003

The problem form retrieves the info from the MSSQL database and puts it in a
repeating table and i can run queries to show only the relevant data but the
main purpose of the form is to get who took the longest or shortest time per
task or day or Activity.

eg..

Date | Name | TIME Taken | Activity

1-Jan-2003 | Jim | 00:15 | Invoicing

4-Jan-2003 | Tony | 00:35 | Labelling

7-Jan-2003 | Jim | 01:00 | Invoicing

01:40 - - - Total Time Taken

The problem is when i try to calculate the Sum of the Time taken it gives a
(1.#QNAN) error.



I have tried the Infopath Blog
http://blogs.msdn.com/infopath/archive/2007/02/21/calculating-elapsed-time-without-code.aspx

But that didnt work just wondering if im doing something wrong or if the
fact that MSSQL store additional irrelevant info such as the date field i
enter gets a time assigned (00:00) and the time field gets a Date assigned
even thoug i dont use those parts could they be affecting the calculation.

 

[Clay Fox]

InfoPath, especially 2003, is week in this area.

I beleive Scott's blog post could work although it is a bit complex.

You can use VBscript and use the data add and some of the other date
functions to do it. There are soem code examples on
http://www.infopathdev.com search on dateadd

It sounds like you really want to do reporting and analysis, InfoPath often
is not the best tool for this. I usually extract the data to SQL and then
use Web Reports or excel to connect and create reports or pivot tables.

 

[sboyd]

I have created a DB in MSSQL and a couple of forms in Infopath 2003

The problem form retrieves the info from the MSSQL database and puts it ina
repeating table and i can run queries to show only the relevant data but the
main purpose of the form is to get who took the longest or shortest time per
task or day or Activity.

eg..

Date           | Name     | TIME Taken | Activity

1-Jan-2003  | Jim         | 00:15          | Invoicing

4-Jan-2003  | Tony       | 00:35          | Labelling

7-Jan-2003  | Jim         | 01:00          | Invoicing

                                   01:40  - - - Total Time Taken

The problem is when i try to calculate the Sum of the Time taken it gives a
(1.#QNAN) error.

I have tried the Infopath Blog  http://blogs.msdn.com/infopath/archive/2007/02/21/calculating-elapsed...

But that didnt work just wondering if im doing something wrong or if the
fact that MSSQL store additional irrelevant info such as the date field i
enter gets a time assigned (00:00) and the time field gets a Date assigned
even thoug i dont use those parts could they be affecting the calculation.

I think I have this figured out. Create an Hour Field, Minute Field
and a Total_Minutes Field Default each to 00. Set the formula for
Total_Minutes as:
(substring(Hour, 1, 2) * 60) + (substring(Minute, 1, 2))

Create a summary expression for a sum of the Total_Minutes Field
column as: sum(Total_Minutes)

Create a summary expression for the total Hours and Minutes as:
concat(string(round((sum(Total_Minutes) / 60) - 1)), ":",
string((sum(Total_Minutes))) - string((round((sum(Total_Minutes) /
60)) - 1)) * 60)

This worked on my form.