How To Calculate Time Duration Between Times After...

Inao_Vasquez · 01-22-2022 09:46 AM

I record calculation of time duration, i have de Start Time, and end time. I calculated the time difference
between the two times by directly minus “=EndTime-StartTime”. However, it does not work properly if the endtime is after midnight.
Please help with the correct expression. Thanks!

Marc_Dillon

Easiest solution is to just use DateTime values instead of Time values.

Inao_Vasquez

The problem with DateTime is that in the form it asked for Date,
making redundant the information in the form. Is there another way?
in Excel it works properly. I found this expression:
=B2-A2+(B2<A2)

However i dont know if something similar could be calculated in Appsheet.

Marc_Dillon

So what if the data is redundant?

Is the user manually inputting the time, or is it being auto-recorded?

Sure there is. I’m advising on what I think is easiest, because I’ve dealt with the same headache before.

Are you recording the start date and end date as well?

I don’t understand what this formula is supposed to do. (B2<A2) would produce a true/false value, and you add that to a difference?

Joseph_Seddik

True/false evaluates to 1/0

Joseph_Seddik

How? what result it gives you? Thanks.

Marc_Dillon

A negative duration.

Like 4am minus 8pm = “-016:00:00”

Joseph_Seddik

Exactly! I was waiting for him to give just that answer

So he only needs to add “024:00:00” to fix it.

Inao_Vasquez

Thanks KJS and Marc for your feedback, Im sorry my delayed answer. here a condensed comments:

I found in Excel, or Google Sheets, there is no problem calculating Duration when the EndTime is after midnight. Just using =B2-A2, Or EndTime-StartTime.

The context, the app allow the user to record the start and end time of several drone flights. There are many flights, when the start and end time is before Midnight so, the solution to +24:00:00 does not fix general case.

Thanks in advance

Marc_Dillon

You would only add 24hrs conditionally.

Inao_Vasquez

Thank you! Im new in syntax. May you help me, please.
If StartTime is “A1”, and End Time “A2”. Conditional duration calculation could be something like this?:

DURATION = IF((A2 > A1), “A2-A1”, “A2-A1+24”)

Thanks!

Marc_Dillon

Yep, exactly.

Ashleigh

How would one write this as an array formula? (if i'm using the wrong word, so i can calculate a whole column? TIA!!

Steve

Please start a new topic for help with this.

Anthony_Martin

But... How do you do this at the AppSheet form level and not at the spreadsheet level?

I try putting ```IF(ISBLANK([END TIME]),"",IF([END TIME]<[START TIME],(1+[END TIME])-[START TIME],[END TIME]-[START TIME]))``` in to the formula section but it doesn't work. This formula works in spreadsheets, but not on the scope of the app level.

I think it's because the time input only does "AM / PM" formatting, so its numbering format doesn't fit the formula validation parameters and I don't quite know how to compensate for it yet.

Austin_QREW

Hi @Anthony_Martin,

This is a great question. We have a Timesheet Template App that uses this feature. The basic setup is a TimeCard Table and a TimeSheet Table. Within the TimeCard Table we have a column called [Calculated Time] with a column type decimal that uses the following expression:

IF(ISBLANK([Time Out]), 0, TOTALHOURS([Time Out]-[Time In]))

This expression ensures that if time out is blank to result to 0 if not then calculate the duration between time in and time out.

Here is the Display Date expression as well that covers the AM/PM question.

IF( ISBLANK([Time Out]),

"In: " & TEXT([Time In], "H:MM AM/PM"),

"In: " & TEXT([Time In], "H:MM AM/PM") & " | Out: " & TEXT([Time Out], "H:MM AM/PM")

)

You can copy & customize this app here to explore more: Timesheet App

We also have an Advanced Logic and Expressions course available on AppSheet Training that covers this app and the full scope of creating advanced expressions in AppSheet.

Lastly, if you would like to connect with our team you can do so by signing up for a Tech Talk here.

Let me know if you have any other questions. Hope this helps.

Happy Developing!

Austin from AppSheet Training

Joseph_Seddik

Put [startTime] as the Initial value of your endTime column. Then:

If you have DateTime columns, then simply:

[endTime] - [startTime]

If you have Time columns:

[endTime] - [startTime] + IF([endTime] < [startTime], "024:00:00", 0)

nigel_williams

Follow this guy for amazing insights on everything spreadsheet https://youtu.be/Xl646S9mQ20 this video is all about time in spreadsheets.

How To Calculate Time Duration Between Times After Midnight?