Round Seconds / Ignore Seconds

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.

0 14 1,317
14 REPLIES 14

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 -
2X_2_2c135de7a15882a4ad029eb05f9eb054bab0e90e.png

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.

Top Labels in this Space