Adding time to time

[DC81]

I would’ve thought this one to be so simple but yet i am pulling my hair out. This is nothing like a spreadsheet…

I need a virtual column to determine wether a time (set via a time picker on my form) is less than (or greater than) a set time (10:30am) and change it’s own value to either 0 or 1 accordingly. This would be a trigger to deduct 15mins from a work day.

In total, I actually need to determine wether a time frame crosses a break of 15mins which starts at 10:30am (and then lunch and afternoon tea)

Any ideas or samples would be greatly appreciated.

Thanks in advance

Dan

[Suvrutt_Gurjar]

Based on the description, it sounds you could take a look at duration rather than time as result.

help.appsheet.com

Date and Time Expressions

Incorporate dates and times into the behavior of your app.

Some peculiarities of time and duration columns to note

1. [Time 1]- [Time 2] gives duration rather than time.

“14:00:00”- “12:30:00” will result in duration of 01:30:00

2. If one tries addition [Time 1] +[Time 2] will give an error as two times cannot be added.

“14:00:00”+ “12:30:00” will give an error for adding two times

3. One can add [Time 1] + [Duration 1] which will give another time

“14:00:00”+ “002:30:00” will resut in 4:30:00 PM , a time
Please note an extra 0 before 02:30:00 above that identifies a duration from time.

[DC81]

I understand what you have said, thanks so much for that.

Is there a way of converting time to a decimal? Spreadsheets will allow this as 24hrs=1 (being 12pm=0.5, 6am=.25 and so on)

If so, I’m assuming I could add, divide, multiply and subtract.

Is this possible?

[Suvrutt_Gurjar · Accepted solution]

You are welcome. There are functions as below that basically convert duration into decimals. With some workarounds as below, these could be deployed for time calculations in decimals.

So TOTALHOURS(“12:00:00 PM”-“00:00:00”)/24 will give 0.50 for 12 PM

TOTALHOURS(“06:00:00 AM”-“00:00:00”)/24 will give 0.25 for 6 AM

TOTALHOURS(“12:00:00 AM”-“00:00:00”)/24 will give 0.00 for 12 AM

In all the expressions above the first parameter is time.

help.appsheet.com

TOTALHOURS()

Count of hours in Duration

help.appsheet.com

TOTALMINUTES()

Count of minutes in Duration

There is also TOTALSECONDS() available.

[DC81]

Awesome. Thanks for the info Suvrutt

[SkrOYC]

---

@Suvrutt_Gurjar wrote:

2. If one tries addition [Time 1] +[Time 2] will give an error as two times cannot be added.

---

This should be added to the docs