Formatting Seconds as time.

[kjcain]

I have a source database that shows usage in a long int field as seconds.

This works great in building a OLAP cube for it shows the time on the phone for each task etc.

We are using Excel as the user interface and allowing the manager to add, remove fields and drill down in various ways to see the data and generate many graphs. This means the cells are never in the same place twice, so formatting in Excel is challenging if not impossible.

Of course who wants to know that the user had 21,356 seconds of use. The manager would like it displayed as HHH:MM:SS

I tried to create a DLL that would format the field, but the cube treated this as a string and this gave many problems.

What is the standard method for dealing with time and formatting to human readable fields?

Thanks!
Kevin Cain

 

[SilentWings]

Hi Cain
We had the same problem but we solve it with the formula bellow. Note that "Duracao" (duration) is a measure that contains the number of seconds.

I hope this hep you.
Best regards

FORMULA:
cstr(cint([Measures].[Duracao] / 3600-0.5)) + ":" + format(cstr(cint(([Measures].[Duracao] - (cint([Measures].[Duracao] / 3600-0.5) * 3600)) / 60-0.5)),"00")+ ":" +
format(cstr(cint(([Measures].[Duracao] - (cint([Measures].[Duracao] / 3600-0.5) * 3600) - (cint(([Measures].[Duracao] - (cint([Measures].[Duracao] / 3600-0.5) * 3600)) / 60-0.5) * 60)))),"00")