A TIME is a specific hour of the day
You should write it in the following format: "00:00:00"
, corresponding to HH:MM:SS
You should include it between quotes ""
in order for the expression to work.
A DURATION refers to the amount of time between two moments, each moment is a TIME.
You should write it in the following format: "000:00:00"
, corresponding to hours:minutes:seconds.
Note the leading 3 digits:* 000
, this is the only way you tell appsheet that this is a DURATION not a TIME (beginning with 2 digits: 00
).
Alternatively, you can also use the following format: "00.00:00:00"
, here you begin with 2 digits then a dot then 2 digits: 00.00
, this corresponds to days.hours:minutes:seconds.
Note that the output of AppSheet will display only two leading digits 00
for a duration value. However, your input should always begin with either three digits 000
, or 2-dot-2 digits 00.00
, to specify a duration.
And you should include it between quotes โโ
in order for the expression to work.
Now, letโs say that TIMENOW()
translates to 9AM. Hereโs how the expressions would work, and how to set the type of your column so you donโt get an error:
TIME + TIME โ This is meaningless.
TIME + DURATION = TIME
โ Example: TIMENOW() + "000:30:00"
= 09:30:00 (9:30AM).
Note the leading 3 zeros: 000
.
DURATION + DURATION = DURATION
โ Example "001:00:00" + "000:30:00"
= 01:30:00 (1 hour 30 minutes)
And,
TIME - TIME = DURATION
โ Example: TIMENOW() - "12:00:00"
= -03:00:00.
The amount of time starting from 12AM to 9AM is -3 hours.
TIME - DURATION = TIME
โ Example: TIMENOW() - "012:00:00"
= 21:00:00.
The TIME 12 hours earlier is 9PM.
DURATION - DURATION = DURATION
โ Example: "001:00:00" - "000:30:00"
= 00:30:00 (30 minutes)
DURATION - TIME โ Meaningless
When would you need that?
One example is that you have Date and Time values in two separate columns, and you need to join them in a single value for an expression, say, for example in a YES/NO
expression to determine whether you are past a certain hour on a specific day.
Another example is when you have a Date only and you need to associate and hour to that date.
Considerations:
A Date-only value is internally a DateTime value, with the Time set to "00:00:00"
, that is Midnight.
A Time-only value is internally a DateTime value, with the Date set to the famous 30/12/1899
.
Solution:
Example:
Suppose you have two separate Date and Time columns. To combine them together, just add a zero-Duration to them:
DateTime value = [Date] + [Time] + "000.00.00"
Hi @KJS
Thanks for sharing this !
You may want to set it as โTips & Tricksโ category, itโs relevant
Also, for additional reference:
EDIT : also, you may want to specify that TIME and DATETIME are pretty similar in their use when it comes to compute with duration
EDIT 2:
!!!
This is new to me! Thanks!
Update - Added the last section: How to transform separate Date and Time values into a combined DateTime value
I still need to be able to calculate duration for times that go past 12 AM into the next day WITHOUT DATETIME , is this possible?
[time2] - [time1] + IF([time2] < [time1], "012:00:00", "000:00:00")
Assuming you have other mechanisms to make sure the end time might only fall in the next day, not after, and you don't get spans exceeding 24 hours, otherwise you should construct DateTime values.
This suggestion returns a Time type for me, the OP also looking for duration.
"TimeType1 timeType2 Time Duration h":"mm (sheets)"
3:30 PM 0:18 AM. 08:48 PM 560.48. 8:48
8:48 is the desired result in sheets
sheets also shows 560.48 when formated as duration.
How do I display, format or convert the appsheet result to a duration?
I recognize this is simple and the answer obvious... but not to me.
Thanks
I would like to share an additional difference between time and duration types when editing: the time format mask avoids typing the ":" separator each time between hh:mm:ss, as is mandatory in the duration type (probably due to the option of adding the days at the beginning).
I work on an App that involves video timecodes, and for operators it is much easier to type in the time format (they only use the numeric pad) even though they are durations.
This may not be ambivalent for other Apps that need to record durations with days.
Thanks!