Need a little formula help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi there,

I'm setting up a formula to calculate hours of vacation based on a schedule
that increments by years of service.

I have the formula set up and working, with the one hitch that when there is
no date of joining entered, the formula enters the highest amount of vacation
allocated. There will eventually be a date of joining for each person on the
list, but for now I would like the formula result to be blank if there is
none.

I think I am missing a [value_if_false] attribute, but I can't figure out
exactly where. Or, I could put another if statement on the final
date-of-joining condition to say if it's null, then print "0", otherwise
carry out the rest of that condition?

Could someone take a look at the formula and lend some advice? My head hurts.

Thank you!


=IF((DATEDIF(J37,NOW(),"y"))>=18,(6*35*F37),IF((DATEDIF(J37,NOW(),"y"))>=12,(5*35*F37),IF((DATEDIF(J37,NOW(),"y"))>=6,(4*35*F37),IF((DATEDIF(J37,NOW(),"y"))>=1,(3*35*F37),"0"))))
 
I think your date of joining is in J37, so you could check for this
being empty first in your formula, as follows:

=IF(ISBLANK(J37),0,IF((DATEDIF(J37,NOW(),"y"))>=18,(6*35*F37),IF((DATEDIF(J37,NOW(),"y"))>=12­,(5*35*F37),IF((DATEDIF(J37,NOW(),"y"))>=6,(4*35*F37),IF((DATEDIF(J37,NOW()­,"y"))>=1,(3*35*F37),"0")))))

However, I'm sure the rest of your formula can be simplified too - you
have >18 years-factor 6, >12 years-factor 5, >6 years-factor 4, i.e.
(INT(year_difference/6) + 4) times 35*F37....

Sorry, I can't think at the moment, but I'll get back to you. The
formula as posted should work.

Hope this helps.

Pete
 
Thanks, Pete. I ended up doing this:

=IF(J37,IF((DATEDIF(J37,NOW(),"y"))>=18,(6*35*F37),IF((DATEDIF(J37,NOW(),"y"))>=12,(5*35*F37),IF((DATEDIF(J37,NOW(),"y"))>=6,(4*35*F37),IF((DATEDIF(J37,NOW(),"y"))>=1,(3*35*F37),"no vacation")))),"please enter join date")

Does that make sense? I assume instead of using ISBLANK() I can just say
if(cell), because it is working... is there any disadvantage to that?

I see what you're saying about factors. The only thing is, I would kind of
like to leave the constants there because they come directly from a
collective agreement -- I'm not sure what kind of changes will happen down
the road. I'll have to think about that.
 
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
 
Back
Top