How would you round a Time value?
We can use โIgnore secondsโ, but this would not round the Time value.
The value 7:26:58 would look like 7:26.
I would like it to round to get 7:27.
Iโm afraid you need to concatenate the time manually and then convert it back to time value. For doing that, you first need to read the minute value together with seconds and then round it up.
Time can be really difficult to deal with in AppSheet. First,
index(split(โ07:26:58โ,":"),3)
produces
58 AM
So, to fix this I need to do the following:
left(index(split(โ07:26:58โ,":"),3))
A bigger problem is that AppSheet continually tries to interpret anything that looks like a time and that has leads to all kinds of errors.
The only way I could get it to work was to divide the task in to three virtual columns, all of which are โDurationโ columns.
Column 1
concatenate(hour(7:26:58),":",minute(7:26:58),":00")
Column 2
if(SECOND(7:26:58)>29,000:01:00,000:00:00)
Column 3 adds the first two columns together. The result is 07:27:00.
However, AppSheet doesnโt let me put the first two expressions together in one expression:
concatenate(hour(7:26:58),":",minute(7:26:58),":00")+if(SECOND(7:26:58)>29,000:01:00,000:00:00)
produces the following error:
Times can be very difficult to deal with in AppSheet.
Thank you very much for your input @Aleksi @Kirk_Masden
Hi @Fabian
As rightly guided by @Aleksi, we need to use expressions to round up.
Please explore the following round off expressions just in case these are of any help toyou
24 hours timeformat rounding off (Coumn in test app RO24 Time)
IF(SECOND([GetTime]-โ00:00:00โ)>30, TEXT([GETTIME]+โ000:01:00โ,HH:mm),TEXT([GETTIME]+โ000:00:00โ,โHH:MMโ))
AM /PM format rounding off (RO AP Time)
IF(SECOND([GetTime]-โ00:00:00โ)>30, TEXT([GETTIME]+โ000:01:00โ,โHH:mm AM/PMโ), TEXT([GETTIME]+โ000:00:00โ,โHH:MM AM/PMโ))
Here [GETTIME] is the input column that has seconds in the time.
Some quick test results -
Awesome @Suvrutt_Gurjar thank you very much.
Thanks @Suvrutt_Gurjar for this great solution. It works for me but when I start to play around with it I sometimes get the โhas inputs of an invalid type โUnknownโโ error I mentioned above. I wonder if you or @Aleksi could help me understand what causes this error and how to avoid it.
P.S. Iโve written about this here too:
hI @Kirk_Masden,
I request you to explore following in case of expressions mentioned by you
As you have mentioned , you are correct that the expressions in two columns evaluate properly when tested individualy. However the Column 1 expression evaluates as text because it has CONCATENATE() function and second one evaluates as duration type. So one cannot perform an arithmatic operationon a text column/expression. ( Adding duration to text column)
To overcome this, if the first columnโs text expression is typecast in TIME ,then it works together as now we add duration to time. Also in the second expression , the recommended format by AppSheet is to put digit based duration additions in double quotes, So overall the following expression works together. Hope this helps.
TIME(concatenate(hour(7:26:58),":",minute(7:26:58),":00")) +(if(SECOND(7:26:58)>29,โ000:01:00โ,โ000:00:00โ))
Thank you very much! I really appreciate your help. I found one problem with the use of TIME(), however, that is that the result is a โtimeโ (with AM or PM) and not a duration.
To convert it to a duration, I imitated some of the expressions you shared with us to make this:
text(TIME(concatenate(hour(7:26:58),":",minute(7:26:58),":00")) +(if(SECOND(7:26:58)>29,โ000:01:00โ,โ000:00:00โ)),โHH:MM:SSโ)
Itโs a bit convoluted but it produces a duration, which is what I was hoping to do. Thanks for your help!
Hi @Kirk_Masden,
Thank you for the update. You are welcome. Nice to know that you came up with the solution you were looking for. I am sorry that I missed that part about duration that you were looking for. Somehow I thought you were also looking for adding duration to time.
No need to apologize. Thanks again for you help!!
Try something likeโฆ
HOUR(TIMENOW()-โ00:00:00โ)&":"&MOD(CEILING(TOTALMINUTES(TIMENOW()-โ00:00:00โ)),60)
Thanks @Aleksi! This works when the number of minutes is two digits but if it is only one it seems to produce a time like โ18:9โ instead of โ18:09โ.
Tryโฆ HOUR(TIMENOW()-โ00:00:00โ)&":"&RIGHT(โ0โ&MOD(CEILING(TOTALMINUTES(TIMENOW()-โ00:00:00โ)),60),2)
Hi @Aleksi,
Very creative expression as usual. Your use of just the right functions is great as usual.
User | Count |
---|---|
41 | |
31 | |
29 | |
16 | |
14 |