Issue with lengt time format

[sergio0592]

Hi all,

I'am facing an issue about formatting a lenght time. I have a difference (Var N / N-1 in the screenshot below) between two lenght. For positive variations, it works (see the first three rows). But in the case of a negative variation, i have ##### and the corresponding number is -0,000365416990416991.

I get the correct value if i multiply this number with Nb seconds in a day (86400).

Do you have an idea what goes wrong??

Thanks

[Anil_Babu_Samineni]

Would you use Fab(Expression) and then check

Meantime, Can you send aplication to work

[rupamjyotidas]

are you using timeformat and number format with the expression.

[sergio0592]

No, in my formula i don't use timeformat or number format. I just calculate average with sum(Lengt time) / sum (Case Lenght time).

But in properties/Numbers, i have Interval as Number Format Settings:

[rupamjyotidas]

Well Time cannot be negative. Probably do something like Anil has suggested. Fabs(YourEquation)

[sergio0592]

Sorry, i forgot to specify that the problem appears only in Excel export!!  In my .qvw it works.

[rupamjyotidas]

Probably hard code the equation format

Interval( Fabs(YourEquation), 'D mm:ss' )

[marcus_sommer]

Per default excel couldn't handle negative times (there is a global setting within the options to change that but it will change also each other dates - therefore it's not recommended to do it). This meant you need some kind of workaround and returning this field as a string-field or you used the above mentioned fabs() approach and adds a further column which showed the "+" or "-" sign (this approach makes it easier if you need to calculate further with these data although you could of course convert a negative time-string into real values, calculate with them and turn it in a string again).

- Marcus

[sergio0592]

Thanks MArcus for your answer. I have tested the solution with user fabs() and '-' ahead but it don't works. Do you consider i'am obliged to put the negative sign '-' in a column apart.

My formula:

if($(I_Lenght_N) - $(I_Lenght_N_1)<0,'-','') & fabs($(I_Lenght_N) - $(I_Lenght_N_1))

[marcus_sommer]

I meant to display it in an additionally column but maybe it's enough to add further spaces to the "-" sign to bypass the excel-interpretation, maybe in this way:

if($(I_Lenght_N) - $(I_Lenght_N_1)<0,' - ','') & fabs($(I_Lenght_N) - $(I_Lenght_N_1))

- Marcus