Question

Forms - OpenFormula Calculations with Time Fields

Forms Version 10

Updated May 12, 2019

asked on April 26, 2017

Hi All,

I'm trying to calculate the difference between two time fields in Forms 10.2 using the calculation feature. The two fields are in a table, as per below:

I want to calculate the difference (in minutes) between Start Time and End Time, then show this in the Minutes column. So far any formula I've tried either shows a calculation error or "0", and as there is no documentation on how the new time/date calculations work I'm struggling to figure it out. If anyone has done this or has any suggestions on a formula I could use, any advise would be appreciated!

Many thanks in advance

Dan

0 0

Answer

SELECTED ANSWER

replied on April 27, 2017

It is bug for time field. We will work on fixing this.

0 0

Replies

replied on April 26, 2017

Hi Daniel,

It may be that the "Time" field type doesn't allow for calculations or requires a specific function similar to a DATEDIF approach. I tested the standard "=SUB(INDEX(Table.End_Time,ROW()),INDEX(Table.Start_Time,ROW()))" formula and it works fine for number and date fields, but as soon as the fields are changed to Time format the calculation no longer runs.

I'll be interested in seeing what the resolution is for this.

Cheers,

Mike

2 0

replied on May 7, 2019

Was there any update on this?

0 0

replied on May 12, 2019

What version for Forms are you using? The bug for use Sub with two time fields was fixed with Forms 10.3.0. The result need to multiply with 24 to get the hours difference. For example, you need to use calculation such as "=Round(SUB(Time2,Time1)*24,1)" or "=Round(SUB(INDEX(Table_1.Time_2,ROW()),INDEX(Table_1.Time_1,ROW()))*24,1)"(the round formula is used in order to make the return value to only contain at most one digit after decimal point. Following is the sample that works for me:

1 0

replied on September 12, 2017

Are there any updates on this?

I am having issues calculating time fields in a table still.

0 0

You are not allowed to follow up in this post.

Question

Question

Forms - OpenFormula Calculations with Time Fields

Answer

Replies

Sign in to reply to this post.