system
December 2, 2011, 4:59pm
#1
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
December 2, 2011, 7:54pm
#2
Hi,
Suppose that the variable that holds the time information (11:00:01) is called [MyTime]. Then:
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))
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"))
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
December 2, 2011, 8:16pm
#3
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
December 2, 2011, 8:24pm
#4
Then this 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)
The DeskI formula just need to be slightly rewritten for WebI.
Marek Chladny (BOB member since 2003-11-27)
system
December 2, 2011, 8:44pm
#5
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
December 2, 2011, 8:52pm
#6
You’re welcome 8)
Marek Chladny (BOB member since 2003-11-27)