How to show HH:MM:SS in the column?

[system]

Hello All,
I have columns in the reports, which shows time which is displaying value like as

27.0
41.0
5.0

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]

Hi,

This old topic can help:

Convert number of seconds to actual time

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]

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:

Number of seconds of respondtime

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

Than create another variable to show HH:MM:SS and place it into the report.

=(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]

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…

@siru

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

Pooja

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]

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]

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]

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]

Hi,

Can you post the exact formula that you use?

---

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

[system]

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

1. Hour Mod= Mod((Average Respondtime/Process time);3600)//(will give you reminder)
2. Hour = ((Average Respondtime/Process time)-[Hour Mod])/3600//(hour)
3. Minute Mod= Mod(Hour Mod;60)//(Here the reminder is seconds)
4. 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]

Hi,

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

Convert number of seconds to actual time

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)