calculate total hours of column

[eugzl]

Hi All.

In my table I have columns: TimeIn, TimeOut, and TimeWork where I calculate difference between TimeIn and TimeOut in column TimeWork using functions TimeToNum and NumToTime in FormCalc. I would like to calculate total hours of column TimeWork . For instance:

TimeIn	TimeOut	TimeWork
05:25	15:25	10:00
07:10	23:20	16:10
09:15	01:30	16:15
	Total Hours	42:25

If that is possible how to do that?

Thanks.

[AlmirRVS]

Yes, it is possible, but I'd rather use JavaScript instead of FormCalc to calculate the Total Hours.

If you could share a sample form with just the table the way you created, or screen shots showing how you have created the table, I would help you with the script.

For example, I would create the table with a repeatable body row to make that easier, like this:

[MinusZero]

I would actually do it in FormCalc.

This is what i did:

I created a repeating table (the row cells are all Date/Time Fields (Object/Binding tab/Data Format set to Time)

I also had a Total numericField and a button to repeat the rows

In the subTotal Calculate (FormCalc) event i have:

var startTime = Time2Num(this.resolveNode("fromTime").formattedValue, "HH:MM")

var finishTime = Time2Num(this.resolveNode("toTime").formattedValue, "HH:MM")

var total = Num2Time(this.resolveNode("subTotal").formattedValue, "HH:MM")

(finishTime - startTime) / 3600000

In the Total Calculate (FormCalc) event i have:

sum(Table1.Row1[*].subTotal[*]) //this calculates the column total as each row is added/edited.

Due to my format, times are typed into the From and To fields using 24 hour time with leading zeros and colon separators.

If you dont use a leading zero, the subtotal time will be wrong.

You can add more rows and the time total will continue to calculate

[eugzl]

Hi MinusZero. Thanks for replay.

My form has fixed 14 rows table with header and footer. The cells of TimeIn and TimeOut columns are formated to HH:MM time fields. The cells of TimeWork column are TextFields. In TimeWork I calculate time like:

if(Table1.Row1.TimeIn.isNull or Table1.Row1.TimeOut.isNull) then

$ = ""

else

var startT = Time2Num(Table1.Row1.TimeIn.formattedValue, "HH:MM")

var endT = Time2Num(Table1.Row1.TimeOut.formattedValue, "HH:MM")

var differenceT = endT - startT

$ = Num2GMTime(differenceT + 1000, "HH:MM")

endif

In the footer of the column TimeWork I would like to calculate total hours of that column and result should display in format HH:MM. How to get correct calculation result if total hours possible will be more than 24 hours?

Thanks.

[MinusZero]

Hi eugzl,

I havent had a lot of fun with time calculations but here is something i worked up to change hours into days and hours. You can modify it further as needed. This might get you closer to what you are after.

I have a NumericField with the hours total and a TextField to display the result. In the exit event of the NumericField (you could put it elsewhere. I have this:

form1.#subform[0].NumericField2::exit - (JavaScript, client)

var hours = NumericField2.rawValue; //You can replace this with an input value 
TextField2.rawValue = (hours / 24 | 0) + " days and " + hours % 24 +" hours";

When i exit the NumericField, it shows the days and hours.