how to minus objects

system · April 2, 2010, 10:38pm

Hello All,

I have two objects in the universe which is date Type objects.

when i drag and drop it will show HH:MM:SS. in both the objects.This is time ojects.

I need to create another variable by doing minus between both objects.

I am getting an error while creating a variable.

Error is as belwo:

The expression /sub -expression at position uses an invalid data type.specify a valid data type

Can you guys tell me how to solve it.

Thanks for your help

Pooja

ktm (BOB member since 2008-11-12)

system · April 3, 2010, 11:41am

Hi,

First convert both strings into numbers that represent the number of seconds. Then do minus beween these 2 numbers and then, if needed, convert the number into hh:mm:ss format again.

Something similar is done here:

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

system · April 5, 2010, 1:21pm

Hello ,

Below is the code of the universe objects:

Cal Time:

IF(TIME_TO_SEC(TIMEDIFF(TimeDSL.responseTime, TimeDSL.DispatchedTime))/60 > 600, null, IF(TIME_TO_SEC(TIMEDIFF(TimeDSL.responseTime, TimeDSL.DispatchedTime))/60 < -600, TIME_TO_SEC(ADDTIME(TimeDSL.responseTime, TIMEDIFF(TIME('24:00:00'), TimeDSL.DispatchedTime)))/60, TIME_TO_SEC(TIMEDIFF(TimeDSL.responseTime, TimeDSL.DispatchedTime))/60))

Response time, and Dispatched time is another objects.

I drop the Response time,Dispathed Time, and Cal time, Both objects(Response time and Dispatched time) is displaying 00:00:00(HH:MM:SS) format. When I drop “Cal time” , It is displaying in Number.I want convert into the same format as Response time and Dispatched time.
(HH:MM:SS)
When i right click and format date and time it is showing an error # Format.

Basically, I want to check “Cal time Object” whether it is doing Minus properly?

Looking forward to hear from You.

Thanks for your help again

Pooja

ktm (BOB member since 2008-11-12)

system · April 5, 2010, 5:22pm

I have two objects in universe, Response time , Dispatched Time which will display HH:MM:SS when i drag and drop in the WEBI.

My requirements is to get the time Difference between the above objects.

I Created one Variable " number of seconds" by which below formula :

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

Than for Formatting Varibale i try to create a varibale but it is throwing an error: Unrecognized inputs’&’ at position 52.

=FormatNumber(Floor([number of seconds]/86400);“0”) & " day(s) " &
FormatNumber(Floor(Mod([number of seconds] ;86400)/3600) ;“00”) & “:” & FormatNumber(Floor(Mod(Mod([number of seconds];86400);3600)/60) ;“00”) & “:” & FormatNumber(Mod(Mod(Mod([number of seconds];86400) ;3600);60) ;“00”)

Please help me to get this resolve.

Thanks
Pooja

ktm (BOB member since 2008-11-12)

system · April 6, 2010, 1:00pm

Hello All,

I have number of seconds variable now, I just want to format in HH:MM:SS.

I used Below Formula but it is not parsing throwing an Errr: Unrecognized inputs’&’ at position 52

=FormatNumber(Floor([number of seconds]/86400);"0") &amp; " day(s) " &amp; 
FormatNumber(Floor(Mod([number of seconds] ;86400)/3600) ;"00") &amp; ":" &amp; FormatNumber(Floor(Mod(Mod([number of seconds];86400);3600)/60) ;"00") &amp; ":" &amp; FormatNumber(Mod(Mod(Mod([number of seconds];86400) ;3600);60) ;"00")

Can you please help me to solve this Issue.

Thanks
Pooja

ktm (BOB member since 2008-11-12)

system · April 6, 2010, 1:44pm

I tried using similar syntax and got the same error, about "unregonised &… However, if I changed “&” to “+”, the rest of the “&” symbols didnt cause any problems… I am wondering why at that certain position it is giving this error?

americanmc (BOB member since 2009-12-31)

system · April 6, 2010, 2:47pm

I Changed the below formula but still have cannot parse it.

=FormatNumber(Floor([number of seconds]/86400);"0")"+"; " day(s) " ; "+"
FormatNumber(Floor(Mod([number of seconds] ;86400)/3600) ;"00")  "+": "+" FormatNumber(Floor(Mod(Mod([number of seconds];86400);3600)/60) ;"00")  "+": "+" FormatNumber(Mod(Mod(Mod([number of seconds];86400) ;3600);60) ;"00")

Can you please look into it.

Thanks
Pooja

ktm (BOB member since 2008-11-12)

system · April 6, 2010, 8:00pm

Hello All,

Can anyone tell me what i need to modify in my formula.I tried various way but could not parse it.
Please let me know.
Thanks
Pooja

ktm (BOB member since 2008-11-12)

system · April 6, 2010, 8:11pm

Pooja,

Please do not bump your post until at least one business day has passed!

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