how to format the field for elapsed time 1

[susanna123]

I have a formula field that calculates the totals or sums the elapsed time. I would like to highlight or create a formatting field that will highlight all elapsed times that are greater than 25 minutes.

This is my formula field for elapsed time at the group footer:

Local NumberVar sec := Sum ({@Elapsed - Number}, {AP_FinishedInstances.SI_Name}) ;

Local NumberVar hrs := Truncate(Remainder(sec,86400)/3600);
Local NumberVar mns := Truncate(Remainder(sec,3600)/60);
Local NumberVar scs := Remainder(sec,60);

ToText(hrs,"00") + ":" + ToText(mns,'00') + ":" + ToText(scs,'00')

I created a formatting formula for font color at the detail level:

This is the code:

if {@Elapsed - Number} > Average ({@Elapsed - Number}, {AP_FinishedInstances.SI_Name})
then crTeal

else

if {@Elapsed - Number} > '00:25:00' then crRed

//if {@Elapsed - Number} < Sum ({@Elapsed - Number}, {AP_FinishedInstances.SI_Name})
//then crRed


Can you tell me what I am doing wrong and how I can correct this?
thanks

 

[lbass]

if {@Elapsed - Number} > Average ({@Elapsed - Number}, {AP_FinishedInstances.SI_Name})then
crTeal else
if {@Elapsed - Number} > 1500 then //# of seconds in 25 minutes
crRed

Not sure what you intended with your last commented out lines:

//if {@Elapsed - Number} < Sum ({@Elapsed - Number}, {AP_FinishedInstances.SI_Name})//then crRed

-LB

 

[susanna123]

Hi Ibass

Thanks for your help.

I also have another question.

I have 2 date values.

Start time and End time

This is the format:

Start:
08/07/2008 10:56:29
End:
08/07/2008 10:56:45

Start:
09/02/2008 14:48:03
End:
09/02/2008 14:48:11

When i calculate the elapsed time
the first start and end time becomes:

00:00:16

and the second becomes:

00:00:08

Is it possible to change the format of the date and time so that it shows this format for

Start:
07/08/2008 10:56:29PM
End:
07/08/2008 10:56:45PM

The date and times are currently showing 24 hour clock.

This is my code:

------------------------------------
End Time

Local StringVar dt := {AP_FinishedInstances.SI_ENDTIME} ;
Local NumberVar yr := ToNumber(dt[1 to 4]) ;
Local NumberVar mo := ToNumber(dt[5 to 6]) ;
Local NumberVar dy := ToNumber(dt[7 to 8]) ;
Local NumberVar hr := ToNumber(dt[9 to 10]) ;
Local NumberVar mn := ToNumber(dt[11 to 12]) ;
Local NumberVar sc := ToNumber(dt[13 to 14]) ;
DateTimeValue (yr, mo, dy, hr, mn, sc)

------------------------------------------
Start Time

Local StringVar dt := {AP_FinishedInstances.SI_STARTTIME} ;
Local NumberVar yr := ToNumber(dt[1 to 4]) ;
Local NumberVar mo := ToNumber(dt[5 to 6]) ;
Local NumberVar dy := ToNumber(dt[7 to 8]) ;
Local NumberVar hr := ToNumber(dt[9 to 10]) ;
Local NumberVar mn := ToNumber(dt[11 to 12]) ;
Local NumberVar sc := ToNumber(dt[13 to 14]) ;
DateTimeValue (yr, mo, dy, hr, mn, sc)


Thank you very much

 

[lbass]

Right click on the formula->format field->date and time->customize->select the 12 hour format, etc.

-LB

 

[susanna123]

Hi LB,

I tried that but do I have to make change to the code?
May I request for an example?

thank you again

 

[lbass]

No, just go into the customize screen and choose the 12 hour format. There is also an option for the am/pm extension. What problem are you having?

-LB

 

[susanna123]

Hi LB,

thanks.

Earlier you help me solve this problem:

if {@Elapsed - Number} > 1500 then //# of seconds in 25 minutes
crRed

In my data I am displaying all the data and only formatting those that have elapsed time greater than 25 minutes.

Is it possible to change my record selection to have only records displayed for over 25 minutes and hide the rest?
This is the code I have but i'm getting syntax errors.
Can you help?
-----------------------------

Elapsed - String at Detail

Local NumberVar sec := DateDiff ('s',{@Instance Start Time} , {@Instance End Time}) ;
//sec := 12500 ;
Local NumberVar hrs := Truncate(Remainder(sec,86400)/3600);
Local NumberVar mns := Truncate(Remainder(sec,3600)/60);
Local NumberVar scs := Remainder(sec,60);

and {@Elapsed - Number} > 1500

ToText(hrs,"00") + ":" + ToText(mns,'00') + ":" + ToText(scs,'00')

 

[lbass]

I don't know what I'm seeing here, as you have an unrelated fragment in the middle of your formula:

and {@Elapsed - Number} > 1500

Please show the content of your formulas (and any nested formulas).

-LB

 

[susanna123]

the content of the formulas:

Date Range formula field


//WhilePrintingRecords ;
Global DateVar Range DateRange ;
DateRange:= (if {?Date Range} = "Last 7 Days" then CurrentDate-7 to CurrentDate
else if {?Date Range} = "Last 30 Days" then CurrentDate-30 to CurrentDate
else if {?Date Range} = "Last 60 Days" then CurrentDate-60 to CurrentDate
else if {?Date Range} = "Last 90 Days" then CurrentDate-90 to CurrentDate
else if {?Date Range} = "Last 120 Days" then CurrentDate-120 to CurrentDate
else if {?Date Range} = "Last Year (ie 365 Days)" then CurrentDate-365 to CurrentDate
else CurrentDate-365 to CurrentDate);
Minimum(DateRange) & " to " & Maximum(DateRange)


---------------------------------------------------------------------------------------------------

Elapsed - Number

DateDiff ('s',{@Instance Start Time} , {@Instance End Time}) ;

---------------------------------------------------------------------------------------------------
Elapsed - String at Group


//Local NumberVar sec := DateDiff ('s',{@Instance Start Time} , {@Instance End Time}) ;

Local NumberVar sec := Average ({@Elapsed - Number}, {AP_FinishedInstances.SI_Name}) ;

Local NumberVar hrs := Truncate(Remainder(sec,86400)/3600);
Local NumberVar mns := Truncate(Remainder(sec,3600)/60);
Local NumberVar scs := Remainder(sec,60);


ToText(hrs,"00") + ":" + ToText(mns,'00') + ":" + ToText(scs,'00')

//ToText(hrs,"00") + ":" + ToText(mns,'00') + ":" + ToText(scs,'00.00')

--------------------------------------------------------------------------------------------
Elapsed - String at Group (Sum)


//Local NumberVar sec := DateDiff ('s',{@Instance Start Time} , {@Instance End Time}) ;

Local NumberVar sec := Sum ({@Elapsed - Number}, {AP_FinishedInstances.SI_Name}) ;

Local NumberVar hrs := Truncate(Remainder(sec,86400)/3600);
Local NumberVar mns := Truncate(Remainder(sec,3600)/60);
Local NumberVar scs := Remainder(sec,60);

ToText(hrs,"00") + ":" + ToText(mns,'00') + ":" + ToText(scs,'00')

//ToText(hrs,"00") + ":" + ToText(mns,'00') + ":" + ToText(scs,'00.00')

--------------------------------------------------------------------------------------------
Elapsed - String at Detail

Local NumberVar sec := DateDiff ('s',{@Instance Start Time} , {@Instance End Time}) ;
//sec := 12500 ;
Local NumberVar hrs := Truncate(Remainder(sec,86400)/3600);
Local NumberVar mns := Truncate(Remainder(sec,3600)/60);
Local NumberVar scs := Remainder(sec,60);

//Local StringVar s_time ;
//s_time := if sec < 90 then ToText(sec,0,'')
//else
//if sec in 90 to 86400 then

ToText(hrs,"00") + ":" + ToText(mns,'00') + ":" + ToText(scs,'00')

---------------------------------------------------------------------------------

Instance End Time

Local StringVar dt := {AP_FinishedInstances.SI_ENDTIME} ;
Local NumberVar yr := ToNumber(dt[1 to 4]) ;
Local NumberVar mo := ToNumber(dt[5 to 6]) ;
Local NumberVar dy := ToNumber(dt[7 to 8]) ;
Local NumberVar hr := ToNumber(dt[9 to 10]) ;
Local NumberVar mn := ToNumber(dt[11 to 12]) ;
Local NumberVar sc := ToNumber(dt[13 to 14]) ;
DateTimeValue (yr, mo, dy, hr, mn, sc)


---------------------------------------------------------------------------------

Instance Start Time

Local StringVar dt := {AP_FinishedInstances.SI_STARTTIME} ;
Local NumberVar yr := ToNumber(dt[1 to 4]) ;
Local NumberVar mo := ToNumber(dt[5 to 6]) ;
Local NumberVar dy := ToNumber(dt[7 to 8]) ;
Local NumberVar hr := ToNumber(dt[9 to 10]) ;
Local NumberVar mn := ToNumber(dt[11 to 12]) ;
Local NumberVar sc := ToNumber(dt[13 to 14]) ;
DateTimeValue (yr, mo, dy, hr, mn, sc)


------------------------------------------------------------thanks for your help again.

 

[lbass]

You should be able to use the following in your record selection formula:

{@Elapsed - Number} > 1500

-LB

 

[susanna123]

That worked out. Many Thanks LB