Subtracting hours after 24 hours, the result is negative

[Carlos_Patricio]

I have a problem subtracting hours after 24 hours, the result is negative
Usually in various tools I solve it this way HOUR(24+Hour End -Hour Start) + Minute(24+Hour End -Hour Start)/60.

This gives me the duration or difference regardless of the time example: 23:00PM - 01Am = 2 hours with this formula.
But in appsheet that same logic doesn’t work for me, is there any other way to do it?

Formula use on appsheet = HOUR(24+[HORA TERMINO]-[HORA INICIO])+MINUTE(24+[HORA TERMINO]-[HORA INICIO])/60

[Bahbus]

If you specify the date along with the time, for example “2/28/2020 01:00:00”-“2/27/2020 23:00:00” then you don’t need to do anything fancy.

[Carlos_Patricio]

Yes I know, it would be much easier, but one of the things that people who must use the applications always ask for, is to spend the least amount of time in filling out data.

And one of the complaints was indeed, because they had to select date many times (2 times).
I can’t leave this automated, because sometimes they deliver dates from other days, or in the case of night shifts, they have to enter the date of day 1 in case it’s before 24 hours and day 2 after that time. The user sometimes enters the dates wrong and generates errors later in the final data.

At this time the database that is handling the application is about 200 columns and more than 10,000 rows, so looking for errors in the delivery of information can be quite exhausting.

One of the solutions I was thinking of was to put an if in the formula if the time is between 00:00:00 and 07:00:00, (date+1 & end time) - (date & start time)

[Bahbus]

Well, beyond the fact that I consider 200 columns extremely cumbersome for one table, I see that there is already a date column involved in some way. They don’t have to enter two separate dates. You just want to use DateTime values in your math.

If EndTime<StartTime
(Date+1 & EndTime) - (Date & StartTime)
Else
(Date & EndTime) - (Date & StartTime)

[Carlos_Patricio · Accepted solution]

Bahbus:

If EndTime<StartTime (Date+1 & EndTime) - (Date & StartTime) Else (Date & EndTime) - (Date & StartTime)

I tried that in various ways at first I had almost given up, but in the end I had to make a few adaptations.

IF([HORA TERMINO]<[HORA INICIO],DATETIME(TEXT([FECHA]+1&" “&[HORA TERMINO]))-DATETIME(TEXT([FECHA]&” "&[HORA INICIO])),[HORA TERMINO]-[HORA INICIO])

IF this condition is true:
…(The value of column ‘HORA TERMINO’) is less than (The value of column ‘HORA INICIO’)
THEN use the value of:
…(DATETIME(
…TEXT(
…The concatenation of all these text values (
…The concatenation of all these text values (
…(The value of column ‘FECHA’ + 1
…" "
…The value of column ‘HORA TERMINO’)) - DATETIME(
…TEXT(
…The concatenation of all these text values (
…The value of column ‘FECHA’
…" "
…The value of column ‘HORA INICIO’))
ELSE use the value of:
…(The value of column ‘HORA TERMINO’ - The value of column ‘HORA INICIO’

[Bahbus]

You could also try:

DATETIME(IF([HORA TERMINO]<[HORA INICIO], [FECHA]+1, [FECHA])&" "&[HORA TERMINO])-
DATETIME([FECHA]&" "&[HORA INICIO])