View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.newusers
Pete_UK
 
Posts: n/a
Default Need a little formula help

I think your formula (as amended by me earlier) can be simplified as
follows:

=IF(ISBLANK(J37),0,IF((DATEDIF(J37,TODAY(),"y"))= 6,((INT(DATEDIF(J37,TODAY(),"y")/6)+3)*35*F37),IF*((DATEDIF(J37,TODAY()*,"y"))=1,( 3*35*F37),"0")))

Basically, the first IF returns 0 if J37 is blank, as requested. The
second IF looks to see if the years of service are greater than or
equal to 6 and if so gives 4 times, 5 times or 6 times 35*F37 in blocks
of 6-year service, and the third IF gives 3 times 35*F37 for between 1
and 5 years' service inclusive. You get "0" with under 1 year's service
(shouldn't this be just 0, i.e. without the quotes ?). I have used
TODAY( ) rather than NOW( ) as you are only interested in dates and not
dates and times.

Hope this helps.

Pete