Expand my Community achievements bar.

Dive into Adobe Summit 2024! Explore curated list of AEM sessions & labs, register, connect with experts, ask questions, engage, and share insights. Don't miss the excitement.

calculate total hours of column

Avatar

Level 5

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:

TimeInTimeOutTimeWork
05:2515:2510:00
07:1023:2016:10
09:1501:3016:15
Total Hours42:25

If that is possible how to do that?

Thanks.

4 Replies

Avatar

Level 1

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:

Capture2.jpgCapture3.jpg

Avatar

Level 7

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

1606989_pastedImage_0.png1606990_pastedImage_1.png

1606991_pastedImage_2.png

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.

1606993_pastedImage_3.png

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

1606995_pastedImage_4.png

Avatar

Level 5

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.

Avatar

Level 7

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.

1608924_pastedImage_0.png

1608986_pastedImage_2.png1608925_pastedImage_1.png