Calculation of duration from two Time columns, if [End Time] after midnight produces negative result

[NotMat]

I'm looking for a workaround to this issue as per subject.

I've found a formula online for my [Duration] column, see below:

[End Time] - [Start Time] + IF([End Time] < [Start Time], 24, 0)

This produces the correct numerical value, but shows as a negative value. Any idea on how to make this a positive value?

Both [End Time] & [Start Time] columns are Time columns, where the [Duration] column is a Duration column.

[Suvrutt_Gurjar · Accepted solution]

Please try with a better AppSheet syntax of 

[End Time] - [Start Time] + IF([End Time] < [Start Time], "024:00:00", "000:00:00")

In AppSheet duration is expressed with an extra "0" vs corresponding time value. So a duration of 24 hours is "024:00:00" whereas a time of 8 hours is "08:00:00" and duration of 8 hours is "008:00:00"

Date and time expressions - AppSheet Help

Excerpts from the article:

Edit: You could also try a shorter expression using IFS() instead of IF()

[End Time] - [Start Time] + IFS([End Time] < [Start Time], "024:00:00")

[NotMat]

Amazing, thanks so much!

[Suvrutt_Gurjar]

You are welcome.

Edit:  Please note that this expression will work for only for times recorded in maximum of two consecutive dates and will fail if the duration is more than 24 hours between start and end time.