TIME DURATION FORMAT

[omerniv]

HI

One of my values is time duration for "CALL WAIT TIME". The values are like: 67:15 or 50:59, hours:min. time format is not suitable since the values could get higher than 24.

Please help, is there a special custom format that I can use?

Thanks

[tomok]

HI

One of my values is time duration for "CALL WAIT TIME". The values are like: 67:15 or 50:59, hours:min. time format is not suitable since the values could get higher than 24.

Please help, is there a special custom format that I can use?

Thanks

You'll have to store that as text because there is no format in Excel (that's how you determine your custom formatting code to use) I know of that'll convert a number to anything like 67:15. Or, you break it into two elements, hours and minutes, and concatenate them in a report, with a colon (:) in between.

[omerniv]

Hi
If I save it as a text I wont be able to sum it up to higher level.
I know there is an option in excel: "[h]:mm". it stores the values as 01/01/1900 00:00 and starts to count hours since.

is it possible to use the same method in tm1?

[rmackenzie]

If I save it as a text I wont be able to sum it up to higher level.?

Maybe you need a measure which is 'Call Wait Time (Seconds)' and then have a string rule to convert it to your preferred format, e.g.:

Code: Select all

['Hours Minutes Seconds'] = S:
	STR ( INT ( ['Call Wait Time (Seconds)'] / 3600 ), 5, 0 ) | ':' |
	STR ( INT ( MOD ( ['Call Wait Time (Seconds)'], 3600 ) / 60 ), 2, 0 ) | ':' |
	STR ( MOD ( MOD ( ['Call Wait Time (Seconds)'], 3600 ), 60 ), 2, 0 );

Or you can do it in TI if string rules aren't an option for you:

Code: Select all

# input seconds
nTimeInSeconds = 1234567;

# calculate hours, minutes, seconds
nHours = INT ( nTimeInSeconds / 3600 );
nMinutes = INT ( MOD ( nTimeInSeconds, 3600 ) / 60 );
nSeconds = MOD ( MOD ( nTimeInSeconds, 3600 ), 60 );

# convert to string
sTimeString = NumberToStringEx ( nHours, '00', '', '' ) | ':' |
  NumberToStringEx ( nMinutes, '00', '', '' ) | ':' | 
  NumberToStringEx ( nSeconds, '00', '', '' );

# debug
AsciiOutput ( 'output.txt', 'Time string for ' | NumberToString ( nTimeInSeconds ) | ' seconds is ' | sTimeString );

[lotsaram]

I would go with Robin's suggestion and have both a numeric measure and a string measure calculated by rule from the numeric. This is what I do for all "logging time" kind of reporting when you get an index value timestamp from some system.

You do need to be careful though with the display conversion as to what source system the index value originates from as for example Unix, TM1 & Excel all have different day zero where the index starts from. There is an excellent post on that topic in the FAQ section on this forum.