Hello, I am adding the new calendar view to a...

[Manuel_DE_PAUW]

Hello, I am adding the new calendar view to an app (good addition!).

I am struggling to do what I consider should be an easy time manipulation. I have a field that contains the start day [Date] and the start time [Heure] and how long it took (in minutes) [Durée (’)]. I added a virtual column to create the end time… [End time]. Events happen in the middle of the day and never last more 6 hours so I can assume the end date is same as start date. So to use the calendar view, I just need to calculate the end time. So I want to make a virtual column [End time] that contains the result I want. Something like :

[Heure]+[Durée (’)]. Of course this throws an error as [Durée (’)] is not formatted as a duration like “000:30:00” for instance as per the onlide doc (https://help.appsheet.com/expressions/expression-types/date-and-time-expressions) . In fact, I do not find any syntax that works… I tried [Heure]+[Durée (’)]/60 hoping that it would magically take into account fractions of hours

precisely but it fails too (it does not throw any error but rounds it to the smallest integer as would do the Floor operator)…So, any idea to calculate an End Time base on a Start Time and a number of minutes? Best, Manu

[Aleksi_Alkio]

You need to convert the minute as a duration with a virtual column. It could be something like… CONCATENATE(FLOOR([DURATION]/60),":",MOD([DURATION],60),":00")

[Manuel_DE_PAUW]

Hi, thanks for the answer! I came to the same conclusion indeed.

In fact I had to work around a few bugs or at least weird behaviors…

[Heure]+[Durée (’)]/60

kind of work but only adds 0, 1 or 2…(integers) to the Time or

[Heure]+CEILING([Durée (’)]/60) does not help…

So, here is the outcome:

If I do directly :

[Heure]+CONCATENATE( CONCATENATE(“00”, FLOOR([Durée (’)]/60)) , if(MOD([Durée (’)],60)>9, CONCATENATE(":",MOD([Durée (’)],60)), CONCATENATE(":0",MOD([Durée (’)],60)) ) , “:00” ) I get an error of type mismatch. So I have to create an intermediary virtual column indeed [FormattedDuration] and set its type to DURATION as you mention:

CONCATENATE( CONCATENATE(“00”, FLOOR([Durée (’)]/60)) , if(MOD([Durée (’)],60)>9, CONCATENATE(":",MOD([Durée (’)],60)), CONCATENATE(":0",MOD([Durée (’)],60)) ) , “:00” ) Only then the [End Time] column can be built and give the expected result: [Heure]+[FormattedDuration]

My feedback after this: time functions documentation should be enriched, and there are probably some lacks in the possibilities offered.

Ex: [Heure]+Duration([Durée (’)],minutes) or so could exist to simply add minutes to a specific time without to go thru the mentioned complex ops.

Best, Manuel

[Aleksi_Alkio]

Yes that’s true. Btw… you don’t need to add those extra zeros with the IF statement. The app formula will recognize them.

[Manuel_DE_PAUW]

Thanks Aleksi!