BusinessObjects Board

Converrt HH:MM:SS into MM

building reports webintelligence
#1

Hello ALL,

I have created a variable called average and used the below formula:

=DaysBetween([On Location Time];[Dispatched Time]) * 86400
+(ToNumber(FormatDate([On Location Time];"HH")) * 3600 +
 ToNumber(Left(FormatDate([On Location Time];"mm:ss") ;2)) * 60 +
 ToNumber(FormatDate([On Location Time] ;"ss")))- (ToNumber(FormatDate([Dispatched Time] ;"HH")) * 3600 + ToNumber(Left(FormatDate([Dispatched Time];"mm:ss");2)) * 60 +
 ToNumber(FormatDate([Dispatched Time] ;"ss")))

Average:

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

The value it is returning as below:

-24:-35:00
-24:-50:00
-24:-41:00

The negative values persist in the “average” variable when the call starts before midnight and ends the next day.

Is there any work around, please help:

I thought to do as below:

1)Dispatched Time-Minute

=FormatDate([Dispatched Time];"mm")

2)On Location Time in Minute

=FormatDate([On Location Time];"mm")

But don’t know how to get the difference because I am getting an error while using a daysbetween formula…

Thanks
SH

SH3230 (BOB member since 2010-10-25)

Pivot table in webi
Calculating the # of minutes between 2 datetime fields
#2

What you should probably do is, effectively, change both datetime values to epoch values before you do the subtraction, then change them back. Anything else requires you to check if the day has changed so that your hours aren’t negative, if the hour changed so your minutes aren’t negative, and so on.

You’ve already got the basic formula down. Days times 86400 plus hours time 3600 plus etc. Do this calculation for each time separately (I would even make them separate variables for extra clarity). But instead of calculating the days between your start times, use a common starting date (say 1/1/2014 if your data doesn’t go too far back, or 1/1/1970 if you really like actual epoch times).

Subtract your dispatch time from the on location time to get the number of seconds between.

Now you always have a positive number (unless some wacky guy shows up before he’s dispatched). Average this number.

Now that you have an average number, convert it back to a hh:mm:ss format by reversing the process, using the code you already have.

Lugh (BOB member since 2009-07-16)

#3

Thanks for your reply. Can you please explain clearly what should I need to do by using the formula?

Thanks
SH

SH3230 (BOB member since 2010-10-25)

#4

Okay, this is pseudo-code. You’ll probably have to monkey with it, because I can never keep the date functions and format in WebI straight.


[Seconds On Location]=DaysBetween('01\01\2014';[On Location Time]) * 86400 
+(ToNumber(FormatDate([On Location Time];"HH")) * 3600 + 
ToNumber(Left(FormatDate([On Location Time];"mm:ss") ;2)) * 60 + 
ToNumber(FormatDate([On Location Time] ;"ss")))

[Seconds Dispatched]=DaysBetween('01\01\2014';[Dispatched Time]) * 86400 
+(ToNumber(FormatDate([Dispatched Time];"HH")) * 3600 + 
ToNumber(Left(FormatDate([Dispatched Time];"mm:ss") ;2)) * 60 + 
ToNumber(FormatDate([Dispatched Time] ;"ss")))

[Seconds to Respond] = Average([Seconds on Location] - [Seconds Dispatched])

[Time to Respond] = FormatNumber([Seconds to Respond]/3600;"00") + ":" + FormatNumber(Mod([Seconds to Respond];3600)/360;"00") + ":" + FormatNumber(Mod([Seconds to Respond];360);"00")

Lugh (BOB member since 2009-07-16)

#5

Its not parsing, could not make a variable.

Thanks
SH

SH3230 (BOB member since 2010-10-25)

#6

Well, I did say you’d have to monkey with it. My first guess of a problem would be declaring the constant for 1/1/2014. That’s probably the wrong format, or you have to use ToDate, or something like that.

Lugh (BOB member since 2009-07-16)