Time format

[system]

I have a database field that’s a varchar. I can’t change it’s format. It displays in my WEBI report as, e.g., 11:00:01. BI has no TimeFormat function that I can use to format it to an actual time. My goal is to format this field as time, then subtract it from the current time from to get time elapsed.

Think this is doable?

Thanks.

---

FSmith37 (BOB member since 2009-11-16)

[system]

Hi,

Suppose that the variable that holds the time information (11:00:01) is called [MyTime]. Then:

1. Create a new dimension called [Seconds_since_midnight_MyTime] using this formula:

=ToNumber(Substr([MyTime];1;2))*3600 + ToNumber(Substr([MyTime];4;2))*60 + ToNumber(Substr([MyTime];7;2))

2. Create a new dimension called [Seconds_since_midnight_now] using the formula:

=ToNumber(FormatDate(CurrentDate();"HH"))*3600+ToNumber(FormatDate(CurrentDate();"mm"))*60+ToNumber(FormatDate(CurrentDate();"ss"))

3. Then this formula:

=[Seconds_since_midnight_now]-[Seconds_since_midnight_MyTime]

gives you the number of seconds elapsed between your time (MyTime variable) and “now”.

---

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

[system]

So that seems right, thank you. I need to translate those seconds into hours and minutes. Can’t seem to figure that out. Any ideas?

---

FSmith37 (BOB member since 2009-11-16)

[system]

Then this 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)

The DeskI formula just need to be slightly rewritten for WebI.

---

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

[system]

I called your third formula v_Total Seconds Elapsed and then my WEBI formula to convert to hours and minutes:

=FormatNumber(Floor([v_Total Seconds Elapsed] /3600) ;“00”) + “:” +
FormatNumber(Floor(Mod([v_Total Seconds Elapsed] ;3600)/60);“00”) + “:” +
FormatNumber(Mod(Mod([v_Total Seconds Elapsed] ;3600) ;60);“00”)

Thank You!

---

FSmith37 (BOB member since 2009-11-16)

[system]

You’re welcome 8)

---

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