Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
BedrockMission!

Learn More

View all

Sign in to view all badges

calculate total hours of column

Avatar

Avatar
Validate 10
Level 2
eugzl
Level 2

Likes

5 likes

Total Posts

171 posts

Correct Reply

5 solutions
Top badges earned
Validate 10
Validate 1
Ignite 5
Ignite 3
Ignite 1
View profile

Avatar
Validate 10
Level 2
eugzl
Level 2

Likes

5 likes

Total Posts

171 posts

Correct Reply

5 solutions
Top badges earned
Validate 10
Validate 1
Ignite 5
Ignite 3
Ignite 1
View profile
eugzl
Level 2

24-10-2018

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.

Accepted Solutions (0)

Answers (4)

Answers (4)

Avatar

Avatar
Validate 1
Level 4
MinusZero
Level 4

Likes

30 likes

Total Posts

326 posts

Correct Reply

54 solutions
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 25
Boost 10
View profile

Avatar
Validate 1
Level 4
MinusZero
Level 4

Likes

30 likes

Total Posts

326 posts

Correct Reply

54 solutions
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 25
Boost 10
View profile
MinusZero
Level 4

28-10-2018

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

Avatar

Avatar
Validate 10
Level 2
eugzl
Level 2

Likes

5 likes

Total Posts

171 posts

Correct Reply

5 solutions
Top badges earned
Validate 10
Validate 1
Ignite 5
Ignite 3
Ignite 1
View profile

Avatar
Validate 10
Level 2
eugzl
Level 2

Likes

5 likes

Total Posts

171 posts

Correct Reply

5 solutions
Top badges earned
Validate 10
Validate 1
Ignite 5
Ignite 3
Ignite 1
View profile
eugzl
Level 2

26-10-2018

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

Avatar
Validate 1
Level 4
MinusZero
Level 4

Likes

30 likes

Total Posts

326 posts

Correct Reply

54 solutions
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 25
Boost 10
View profile

Avatar
Validate 1
Level 4
MinusZero
Level 4

Likes

30 likes

Total Posts

326 posts

Correct Reply

54 solutions
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 25
Boost 10
View profile
MinusZero
Level 4

25-10-2018

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

Avatar
Affirm 1
Level 1
AlmirRVS
Level 1

Likes

0 likes

Total Posts

2 posts

Correct Reply

1 solution
Top badges earned
Affirm 1
View profile

Avatar
Affirm 1
Level 1
AlmirRVS
Level 1

Likes

0 likes

Total Posts

2 posts

Correct Reply

1 solution
Top badges earned
Affirm 1
View profile
AlmirRVS
Level 1

25-10-2018

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