Formula for subtracting two time entrys in confiforms

[Tony Gump]

Hello,

I'm attempting to subtract two time entries using the confiforms "calculated" field option. Thanks to another user question and reply from a @Davin Studer @Davin Studer I used this> "(([entry.Timeclientrequested]-[entry.firstattempt]) /3600000)" thinking I could use milliseconds in an hour and accomplish the same thing as the other user did using milis. in a day. No dice though, the format is AM PM for time, maybe that's throwing it off?

This would be a great help, thanks so much!

[Davin Studer · Accepted solution]

What field types are Timeclientrequested and firstattempt?

[Tony Gump]

Time

Thank you.

[Tony Gump]

Both are 'time'

[Davin Studer]

On the back side it stores them both as milliseconds. So you can just subtract the lower from the higher and then divide by 3600000 to get the hours. Like this ...

([entry.time2]-[entry.time1])/3600000

Here is an example Confiforms form. You'll need the Confluence Source Editor to use the below page storage format.

<ac:structured-macro ac:macro-id="546d39d2-f562-4801-a2dd-43d5583498cc" ac:name="confiform" ac:schema-version="1">
 <ac:parameter ac:name="formName">test</ac:parameter>
 <ac:rich-text-body>
 <p>
 <ac:structured-macro ac:macro-id="4df68dfa-e6be-4169-882b-f312264f02a2" ac:name="confiform-field-definition" ac:schema-version="1">
 <ac:parameter ac:name="fieldName">time1</ac:parameter>
 <ac:parameter ac:name="fieldLabel">Time1</ac:parameter>
 <ac:parameter ac:name="type">time</ac:parameter>
 </ac:structured-macro>
 </p>
 <p>
 <ac:structured-macro ac:macro-id="bf652e11-9a6c-4798-b279-26bace085313" ac:name="confiform-field-definition" ac:schema-version="1">
 <ac:parameter ac:name="fieldName">time2</ac:parameter>
 <ac:parameter ac:name="fieldLabel">Time2</ac:parameter>
 <ac:parameter ac:name="type">time</ac:parameter>
 </ac:structured-macro>
 </p>
 <p>
 <ac:structured-macro ac:macro-id="b2664f59-11a9-4e55-8b1f-3cb381f1581c" ac:name="confiform-field-definition" ac:schema-version="1">
 <ac:parameter ac:name="fieldName">calc</ac:parameter>
 <ac:parameter ac:name="fieldLabel">Calc</ac:parameter>
 <ac:parameter ac:name="extras">([entry.time2]-[entry.time1])/3600000</ac:parameter>
 <ac:parameter ac:name="type">formula</ac:parameter>
 </ac:structured-macro>
 </p>
 <ac:structured-macro ac:macro-id="d5f1e3cd-c570-4f2b-aeec-98ef243bf1a2" ac:name="confiform-entry-register" ac:schema-version="1">
 <ac:parameter ac:name="atlassian-macro-output-type">INLINE</ac:parameter>
 <ac:rich-text-body>
 <p>
 <br/>
 </p>
 </ac:rich-text-body>
 </ac:structured-macro>
 </ac:rich-text-body>
</ac:structured-macro>
<ac:structured-macro ac:macro-id="da51ad47-92fe-42a1-bf32-875d9677d1f7" ac:name="confiform-table" ac:schema-version="1">
 <ac:parameter ac:name="formName">test</ac:parameter>
 <ac:rich-text-body>
 <p>
 <ac:structured-macro ac:macro-id="59f5a180-5e0a-40e5-b8b2-a8c82bfb0e29" ac:name="confiform-field" ac:schema-version="1">
 <ac:parameter ac:name="fieldName">time1</ac:parameter>
 </ac:structured-macro>
 </p>
 <p>
 <ac:structured-macro ac:macro-id="b4e4e852-b287-464e-ab71-753f64721ae4" ac:name="confiform-field" ac:schema-version="1">
 <ac:parameter ac:name="fieldName">time2</ac:parameter>
 </ac:structured-macro>
 </p>
 <p>
 <ac:structured-macro ac:macro-id="77422250-54d8-4fed-a1fe-07f428f5c4b1" ac:name="confiform-field" ac:schema-version="1">
 <ac:parameter ac:name="fieldName">calc</ac:parameter>
 </ac:structured-macro>
 </p>
 <ac:structured-macro ac:macro-id="1aa07765-21a2-4cc8-81f2-adbf774b136e" ac:name="confiform-entry-edit" ac:schema-version="1">
 <ac:parameter ac:name="compactMode">true</ac:parameter>
 <ac:parameter ac:name="atlassian-macro-output-type">INLINE</ac:parameter>
 <ac:rich-text-body>
 <p>
 <br/>
 </p>
 </ac:rich-text-body>
 </ac:structured-macro>
 </ac:rich-text-body>
</ac:structured-macro>

[Tony Gump]

Thank you so much! My error using a "calculated" field instead of a "formula" field. The source code helped a ton! 

I had to use a free trial for a different source editor because the free one doesn't show the button on confluence version 6.7, even after making sure it was visible to all users. 

[Davin Studer]

Yeah, calculated fields are only calculated once when the entry is created. Formula fields are calculated on each save.

[Tony Gump]

@Davin Studer I can't see my own reply to your question, is that normal? They are both 'Time' fields where the user selects a time represented as am/pm format.