difference between 2 datetime fields.

  • 1
  • Question
  • Updated 7 months ago
  • Answered
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"
Photo of Bill Fox

Bill Fox

  • 7,954 Points 5k badge 2x thumb

Posted 7 months ago

  • 1
Photo of Mike Dwyer

Mike Dwyer

  • 3,390 Points 3k badge 2x thumb
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.
Photo of Luzie Baumgart

Luzie Baumgart, Official Rep

  • 1,310 Points 1k badge 2x thumb
We were working on something similar and can provide a full sample page for you. Please find the XML in the following reply. Here you can find its explanation. I hope this is helpful, please let me know if there are any questions :-)

If you preview the page, you can see three columns.

Input
Please select any date/time for start and stop. All other fields are calculated automatically.

Total Duration
Here you can find fields which calculate the total duration in different units. Mike was on a good way already. Luckily, you can use the "%" to combine "divide, truncate, subtract, etc.", more information will follow in the final Duration formula at the end.

Total duration in seconds
({{stop}}-{{start}})/1000
We have to divide the difference by 1000 because the date&time format is in milliseconds and we want to get the duration seconds.

Total duration in minutes
({{stop}}-{{start}})/(1000*60)
Like above and multiply the divisor by 60 to get the result on minutes. We could have divided by 60000 but we kept the 1000 and 60 for better comprehensibility.

Total duration in hours
({{stop}}-{{start}})/(1000*60*60)
Like above and multiply the divisor again by 60 to get the result in hours.

Total duration in days
({{stop}}-{{start}})/(1000*60*60*24)
Like above and multiply the divisor by 24 to get the result in days.

Duration
This is the final formula: The number of the whole days, the rest in whole hours, the rest in whole minutes and the text information in between (that's why the field's Formula return type is Text).

FLOOR({{TotalDurationInDays}})+' days, '+FLOOR({{TotalDurationInHours}}%24)+' hours, '+FLOOR({{TotalDurationInMinutes}}%60)+' minutes'

The function FLOOR rounds down the number of total duration in days. For the rest of hours (which are less than a total day) we need a slightly different function. The "%24" does the following:

(1) divide the number of total duration in hours by 24
(2) take only the decimal part and multiply with 24

Sample:
30 hours / 24 = 1.25 (one whole day and 0.25 days)
0.25 day * 24 = 6 hours

Since the result will not always be integer and we only want to know the whole hours, we use the function FLOOR again to round down.

The minutes is calculated the same way, using %60 here. It shows the rest of minutes which are less than a total hour. The result is also rounded down to the next integer.


More background information
This is a UI-Only model and with UI-only fields so that you can easily copy the XML and use it to create a new page in your Skuid composer for testing.

If you want to show the total duration in one unit but you don't like the many decimals, please don't change the settings of the existing "TotalDurationIn..." fields. They need the decimals for the calculation. However, if you like to display only two decimals, you could create a new field and use a formula to show the other field's content (e.g. TotalDurationInMinutes) with only two decimals.

TotalDurationInDays, TotalDurationInHours, TotalDurationInMinutes in TotalDurationInSeconds are auxiliary fields. We created them to display the total duration in different units, which might come in handy in another project. You could create the final "Duration" formula using only the date&time information of the "start" and "stop" fields and the functions, however it would be a longer formula which is usually harder to comprehend and troubleshoot. But it's possible:

FLOOR(({{stop}}-{{start}})/(1000*60*60*24))+' days, '+FLOOR((({{stop}}-{{start}})/(1000*60*60))%24)+' hours, '+FLOOR((({{stop}}-{{start}})/(1000*60))%60)+' minutes'

It could be shortened by multiplying the divisors, but then again it's no longer obvious where the numbers are coming from.
Photo of Mike Dwyer

Mike Dwyer

  • 3,390 Points 3k badge 2x thumb
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.
Photo of Luzie Baumgart

Luzie Baumgart, Official Rep

  • 1,310 Points 1k badge 2x thumb
<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>
Photo of Matthew Leahy

Matthew Leahy, Employee

  • 592 Points 500 badge 2x thumb
Luzie, this is a fantastic share for the community. Thanks for all your time putting this together.