system
June 19, 2010, 1:21am
#1
Hello All,
I have columns in the reports, which shows time which is displaying value like as

etc.

This is a variable we used as below: we want to display HH :MM:SS.

`Average Respondtime/Process time.`

Thanks
Pooja

ktm (BOB member since 2008-11-12)

system
June 19, 2010, 5:18am
#2
Hi,

This old topic can help:

We have a value in our database that is the number of seconds past midnight.
Is there a way to convert this to hh:mm:ss in Business Objects (hh:mm would suffice)
Many thanks in anticipation
caz (BOB member since 2007-04-11)
Marek Chladny (BOB member since 2003-11-27)

system
June 20, 2010, 10:05pm
#3
Hello Marek,

Thanks for your reply,

So, to convert Respondtime which is measure in universe object .I need to use the below formula to determine the number of seconds of Respondtime:

```
=FormatNumber(Floor([Respondtime]/3600);"00 :") +
FormatNumber(Floor(Mod([Respondtime];3600)/60);"00 :") +
FormatNumber(Mod(Mod([Respondtime];3600);60);"00")
```

```
=(ToNumber(FormatDate([Number of seconds];"HH")) * 3600 +
ToNumber(Left(FormatDate([Number of seconds];"mm:ss") ;2)) * 60 +
ToNumber(FormatDate([Number of seconds] ;"ss")))
```

Correct me if i am wrong?

Thanks for your help.

Pooja

ktm (BOB member since 2008-11-12)

system
June 21, 2010, 12:50am
#4
Hi,
I assume the numbers 5.0,27.0 and 41.0 are in seconds, is that right ? and then you want to show those secâ€™s as HH:MM:SS ?

If yes, then your first formula is enought to get the time in HH:MM:SS formatâ€¦

Above formula would return the values as:

```
Respondtime Respondtime(HH:MM:SS)
5.0 00:00:05
27.0 00:00:27
41.0 00:00:41
```

forgotUN (BOB member since 2006-12-13)

system
June 21, 2010, 1:33am
#5
Thanks for your reply.

But i want to display the HH:MM:SS in the cell as below:

```
Respondtime Respondtime(HH:MM:SS)
5.0 00HH:00MM:05SS
27.0 00HH:00MM:27SS
41.0 00HH:00MM:41SS
```

Thanks
Pooja

ktm (BOB member since 2008-11-12)

system
June 22, 2010, 12:17am
#6
Hi,
This should work

```
=FormatNumber(Floor([Respondtime]/3600);"00HH:") +
FormatNumber(Floor(Mod([Respondtime];3600)/60);"00MM:") +
FormatNumber(Mod(Mod([Respondtime];3600);60);"00SS")
```

forgotUN (BOB member since 2006-12-13)

system
January 17, 2011, 8:08pm
#7
Hi,

I have similar issue. Iâ€™m able to get time in HH:MM:SS format but second does not convert to minute. for example i have 00:02:60 , which should be 00:03:00 .

Appreciate your help in advance.

Thanks
Moons

moons (BOB member since 2007-11-08)

system
January 17, 2011, 8:20pm
#8
Hi,

Can you post the exact formula that you use?

Marek Chladny (BOB member since 2003-11-27)

system
January 18, 2011, 8:25am
#9
hi Marek

Can you confirm if it the output is in seconds or minutes or hours?
If in seconds you have to create 4 variables

Hour Mod= Mod((Average Respondtime/Process time);3600)//(will give you reminder)
Hour = ((Average Respondtime/Process time)-[Hour Mod])/3600//(hour)
Minute Mod= Mod(Hour Mod;60)//(Here the reminder is seconds)
Minute = ([Hour Mod]-[Minute Mod])/60 //(Minutes)
then you can use concatenation function as ([Hour]+":"+[Minutes]+":"+[Minutes Mod]).

Hope this will help you
RD

rupakdasatos (BOB member since 2009-07-06)

system
January 18, 2011, 8:45am
#10
Hi,

Some time ago I wrote this formula for converting the number of seconds to HH:MM:SS format:

We have a value in our database that is the number of seconds past midnight.
Is there a way to convert this to hh:mm:ss in Business Objects (hh:mm would suffice)
Many thanks in anticipation
caz (BOB member since 2007-04-11)
The formula is for DeskI and it should not be a problem to re-write it for WebI.

Marek Chladny (BOB member since 2003-11-27)