Help on formula

[VBABEGINER]

Hi All,
I don't understand what goes wrong with this formula..

[TABLE="width: 366"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Col AS2[/TD]
[TD]Col AT2[/TD]
[TD]Col AU2[/TD]
[/TR]
[TR]
[TD]Renewal[/TD]
[TD]68:43:42[/TD]
[TD]formula[/TD]
[/TR]
[TR]
[TD]Col AS3[/TD]
[TD]Col AT3[/TD]
[TD]Col AU3[/TD]
[/TR]
[TR]
[TD]Renewal[/TD]
[TD]36:17:57[/TD]
[TD]formula[/TD]
[/TR]
</tbody>[/TABLE]

formula is

=IF(AND($AS2="Renewal",$AT2<=TIMEVALUE("24:00:00")),"Achieved",IF(AND($AS2="Renewal",$AT2>TIMEVALUE("24:00:00")),"Not Achieved","NA")))


=IF(AND($AS3="Renewal",$AT3<=TIMEVALUE("24:00:00")),"Achieved",IF(AND($AS3="Renewal",$AT3>TIMEVALUE("24:00:00")),"Not Achieved","NA")))

output getting..
Not Achieved
Not Achieved

Why less than 24 hour not captured properly..??

 

[AlanY]

will that work?

=IF(AND($AS2="Renewal",$AT2<=1),"Achieved",IF(AND($AS2="Renewal",$AT2>1),"Not Achieved","NA"))

 

[jproffer]

I'm not all that familiar with timevalue, but according to the wizard it will never be more than 1 or less than zero. It assigns a decimal value to a time...if I'm understanding right. I tried it on "10:00 PM" and it showed a value of 0.91.....

 

[VBABEGINER]

Hi Both, thanks for reply..

Hey Alan,
Why used 1..?
do u mean 1 = 24 hour..?

will that work?

=IF(AND($AS2="Renewal",$AT2<=1),"Achieved",IF(AND($AS2="Renewal",$AT2>1),"Not Achieved","NA"))

 

[AlanY]

Hi Both, thanks for reply..

Hey Alan,
Why used 1..?
do u mean 1 = 24 hour..?

yes, exactly

 

[VBABEGINER]

Not working solution.. Alan

 

[AlanY]

what will you get with

=AT2

?

 

[VBABEGINER]

AT2 contains another formula..

=(NETWORKDAYS($AG2,$AH2,Lookup_table!$J$2:$J$9)-1)*("19:00"-"7:00")+IF(NETWORKDAYS($AH2,$AH2,Lookup_table!$J$2:$J$9),MEDIAN(MOD($AH2,1),"19:00","7:00"),"19:00")-MEDIAN(NETWORKDAYS($AG2,$AG2,Lookup_table!$J$2:$J$9)*MOD($AG2,1),"19:00","7:00")

which outputs..

68:43:42

 

[VBABEGINER]

Any help on this..

 

[VBABEGINER]

ok. now i have design formula like this..

IF(AND($AS2="Renewal",INT(AT2)<24),"Achieved",IF(AND($AS2="Renewal",INT(AT2)>24),"Not Achieved","NA")))

and, values which are less than 24:00:00, getting correct output "Achieved".

but values like..

390:16:48
36:17:57
28:05:09
24:57:14

also getting "achieved"...How is this possible..?? Can any one correct me on this..