I’ve noticed that the formula given to calculate the difference between two time values in the documentation doesn’t work great when trying to calculate the duration of someones sleep (for example).
When the [Start Time] = “13:00:00” and [End Time] = “15:00:00” the DURATION result is correct. But when we have a [Start Time] = “22:00:00” and [End Time] = “07:00:00” the result is “-09:00:00”…
I understand that Appsheet doesn’t understand that there’s a new day that started between 23:00 and 07:00
Ideally i would need something like a DATETIME_DIFF() expression just like you would have in Airtable so you would be able easily calculate this.
Has any body else solved this? Any good approaches i could try?
Set column types for Start and Stop to Time and the calculated time value in a virtual column of type Duration
Running a test AppSheet will show the negative values initially but eventually catches up to show the correct valuation either on save or sync or just waiting a second.
Here’s a quick visual of what I tested. I’d suggest you are on the right path. With a start time of 6.01AM and a stop time of 6.00AM AppSheet initally displays -24:01:00 then rights itself and displays the correct value of 23:59:100:
@Jon_S @Adam_Lawton
In software development or programming languages in general, there is no separate time and date values, there is only datetime actually. Therefore; though it’s not displayed, time and date values are considered and calculated as Joda Date/Time. Briefly;
Time: 12/30/1899 15:00:00.000 (15:00 being your time)
Date: 6/10/2019 12:00:00.000 (6/10/2019 being your date)
For example;
Your start time: 22:00
Your end time: 07:00
AppSheet will calculate the Duration in negative (-15) because the Joda Date of these times are the same, though we logically can estimate that the work had ended the next day morning and expect the result to be positive (+9).
As a result; as @Aleksi had already mentioned above, it’s always a better choice to use DateTime values when calculating durations.
