I can’t for the life of me believe that something so simple (unlike every other possible unit, time is universal) can be so difficult. AS has all kinds of conversion from a duration, but what about going the other direction? I mean, even the TIME() help itself has to work around this using FLOOR(),MOD(), etc?!?!
My users/admin want to add new sessions to a schedule by declaring the start time and the duration (expressed in minutes). I honestly don’t know that I care what data type/format the datasource will save said duration, but since all of our values are in 5 minute intervals/integers, it feels to me like storing the value as a number is more appropriate and then let AS manipulate behind the scenes as such.
The simplest form I have figured out to use the value of minutes (as Number) is:
TIME(CONCATENATE(“00:”,[Duration In Minutes],“:00”))-“00:00:00”
Am I missing something somewhere that would be easier yet? Don’t get me wrong, once I add a virtual column for this information, its not something I need to go back to really, so I am fine if this is the answer, just struggling believing there is nothing more straightforward.
@AleksiAlkio interesting… if I create a new virtual column with your expression only and set that as a duration, yep sure enough, there is an implied concatenation there and that is understood to be a duration and I can easily use it in other column formulas, such as Actual Column, as Number: [Duration in minutes]
Actual Column, as Time [Start Time]
Virtual Column, as Duration [Duration Calc] = “00:”&[Duration in minutes]&“:00”
Actual Column, as Time [End Time] = [Start Time] + [Duration Calc]
However, I do not know of a way to get that same expression to be recognized as a duration directly in the formula for a Type: Time , as the following does not work:
Actual Column, as Time [End Time] = [Start Time] + (“00:”&[Duration in minutes]&“:00”) just results in [Start Time], thus the implicit concatenation (or even explicitly stated as (Concatenate(“00:”&[Duration in minutes]&“:00”)) )
Yeah, understood and agreed its a slightly simpler expression. My follow-up question was around usage in a formula for a column of type Time. I recognize that performing math around a Time type requires that part of the expression to be either Time or Duration type. Since I’m trying to calculate the End Time as a function of Start Time, Duration is clearly what I need. Why I don’t quite get is why “00:”&[Duration in minutes]&“:00” resolves to a format which should be inferred as a Duration, but does not work in the formula. Specifically, [Start Time] + “000:45:00” works fine
A Duration cannot be constructed from a Text expression, except when used in a Duration-type column. You can construct a Duration from two Time values, and Time values can be constructed from a Text expression:
([Start Time] + (TIME(“00:”&[Duration In Minutes]&“:00”) - “00:00:00”))
