difference between 2 datetime fields.

BillBill 💎
edited September 3, 2019 in Questions
I have 2 date/time fields.  What would be the formula for a UI-field to:
1) Calculate the difference in minutes.
2) Take the minutes and convert to "X days, Y Hours, Z Minutes"

Comments

  • edited September 3, 2019
    Minutes attended ---
    skuid: ( {{Time_Left__c}} - {{Time_Arrived__c}} ) / 1000 / 60
    salesforce: (Time_Left__c - Time_Arrived__c) * 24 * 60

    Days/Hours/Minutes will divide minutes by 60, truncate, subtract from the minutes.
    rinse and repeat with hours and days - it gets tedious. I don't think I have an example.
    Between formulas on the object and formulas (formulae?) on the model you might have the necessary functions available to push the calculation through a series of ui-only fields.
  • Luzie BaumgartLuzie Baumgart 🛠️ 
    edited April 17, 2018
    <skuidpage personalizationmode="server" showsidebar="false" useviewportmeta="true" showheader="false" globalfeedbackenabled="false">
        <models>
            <model id="Information" query="true" createrowifnonefound="true" datasource="Ui-Only" processonclient="true">
                <fields>
                    <field id="start" displaytype="DATETIME" ogdisplaytype="TEXT" defaultvaluetype="fieldvalue" defaultValue="2018-04-10T12:00:00.000Z" label="start"/>
                    <field id="stop" displaytype="DATETIME" ogdisplaytype="TEXT" defaultvaluetype="fieldvalue" defaultValue="2018-04-12T15:15:00.000Z" label="stop"/>
                    <field id="TotalDurationInSeconds" displaytype="FORMULA" ogdisplaytype="TEXT" readonly="true" returntype="DOUBLE" precision="17" scale="9" label="in seconds">
                        <formula>({{stop}}-{{start}})/1000</formula>
                    </field>
                    <field id="TotalDurationInMinutes" displaytype="FORMULA" ogdisplaytype="TEXT" readonly="true" returntype="DOUBLE" precision="17" scale="9" label="in minutes">
                        <formula>({{stop}}-{{start}})/(1000*60)</formula>
                    </field>
                    <field id="TotalDurationInHours" displaytype="FORMULA" label="in hours" ogdisplaytype="TEXT" readonly="true" returntype="DOUBLE" precision="17" scale="9">
                        <formula>({{stop}}-{{start}})/(1000*60*60)</formula>
                    </field>
                    <field id="TotalDurationInDays" displaytype="FORMULA" label="in days" ogdisplaytype="TEXT" readonly="true" returntype="DOUBLE" precision="17" scale="9">
                        <formula>({{stop}}-{{start}})/(1000*60*60*24)</formula>
                    </field>
                    <field id="Duration" displaytype="FORMULA" label="Duration" defaultvaluetype="fieldvalue" ogdisplaytype="TEXT" readonly="true" returntype="TEXT">
                        <formula>FLOOR({{TotalDurationInDays}})+' days, '+FLOOR({{TotalDurationInHours}}%24)+' hours, '+FLOOR({{TotalDurationInMinutes}}%60)+' minutes'</formula>
                    </field>
                </fields>
                <conditions/>
                <actions/>
            </model>
        </models>
        <components>
            <basicfieldeditor showheader="true" showsavecancel="false" showerrorsinline="true" model="Information" uniqueid="sk-2qrm-745" mode="edit">
                <columns>
                    <column width="33.3%">
                        <sections>
                            <section title="Input" collapsible="no">
                                <fields>
                                    <field id="start" uniqueid="sk-2qrm-753"/>
                                    <field id="stop" uniqueid="sk-2qrm-756"/>
                                </fields>
                                <renderconditions logictype="and"/>
                            </section>
                        </sections>
                    </column>
                    <column width="33.3%">
                        <sections>
                            <section title="Total duration (different units)" collapsible="no">
                                <fields>
                                    <field uniqueid="sk-3DaF-2106" id="TotalDurationInSeconds"/>
                                    <field uniqueid="sk-3DaF-2112" id="TotalDurationInMinutes"/>
                                    <field uniqueid="sk-3DaF-2118" id="TotalDurationInHours"/>
                                    <field uniqueid="sk-3DaF-2124" id="TotalDurationInDays"/>
                                </fields>
                            </section>
                        </sections>
                    </column>
                    <column width="33.3%">
                        <sections>
                            <section title="Total duration (Text)" collapsible="no">
                                <fields>
                                    <field uniqueid="sk-2_L4-2541" id="Duration"/>
                                </fields>
                            </section>
                        </sections>
                    </column>
                </columns>
            </basicfieldeditor>
        </components>
        <resources>
            <labels/>
            <javascript/>
            <css/>
            <actionsequences uniqueid="sk-2qrm-202"/>
        </resources>
        <styles>
            <styleitem type="background" bgtype="none"/>
        </styles>
    </skuidpage>
  • Matthew LeahyMatthew Leahy ✭✭✭
    edited April 17, 2018
    Luzie, this is a fantastic share for the community. Thanks for all your time putting this together.
  • edited April 17, 2018
    Nicely done! I hadn't thought of using FLOOR() in that way; it's perfect for the job.
    Also, as you suggested, the results of a formula in one ui-only field can be passed on to the formula in another ui-only field. This not only clarifies the logic but allows the intermediate results to displayed (presumably for debugging) if desired.
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!