Duration between two Time values

[Jon_S]

Hi everyone,

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).

Here’s the formula:
IF(([StopTime] > [StartTime]), ([StopTime] - [StartTime]), (([StopTime] + 24) - [StartTime]))

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?

Many thanks in advance

[Aleksi]

The best option is if you change these two columns as DateTime type. Then the calculation will be correct automatically.

[Jon_S]

This worked like a charm. I had a sneaky suspicion this might have been the issue. Works great!

Thanks

[Adml]

Hi Jon,

I tested out and slightly changed your formula - it’s really no better - just what I arrived at

IF(([StartTime] > [StopTime]), (([StopTime] - [StartTime] + 24) ), ([StopTime] - [StartTime]))

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:

Cheers
Adam

[LeventK]

@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.

[Aleksi]

You’re welcome

[Jon_S]

As a side note - it would be good to update the documentation for this topic because that’s what confused me initially

[Steve]

Updated.

[Paras_Sood]

Hi

Is it possible to compare the duration in Appsheet?

I am trying to set a format rule which would make the item red coloured if the duration is more than 8 hours

I'm looking at something like (DateTime Finish - DateTime Start) > 08:00:00

[Paras_Sood]

Just found out you can use ([DateTime Finish] - [DateTime Start]) > "008:00:00"

[SkrOYC]

08:00:00 -> 08:00 AM
008:00:00 -> 8 hours