Multiple IF Function
 

I am trying to calculate accrued vacation. Our employees accrue 6.667
hours/month. For someone who has more than one year of service, I have the
following formula:

=(IF(U11,MONTH(E8)*6.667,U1*6.667)-6.6667)

U1 = Years of service
E8 = TODAY()

I need to add a nested function to the above which would calculate the
accrual if Cell U1 is <1. Can anybody help?

Multiple IF Function
 

Teri wrote:
I am trying to calculate accrued vacation. Our employees accrue 6.667
hours/month. For someone who has more than one year of service, I have the
following formula:

=(IF(U11,MONTH(E8)*6.667,U1*6.667)-6.6667)

U1 = Years of service
E8 = TODAY()

I need to add a nested function to the above which would calculate the
accrual if Cell U1 is <1. Can anybody help?

You are already handling U<=1 with
U1*6.667

So I presume you want that to apply to U=1 only and do something
different for U<1:

=(IF(U11,MONTH(E8)*6.667,IF(U=1,U1*6.667,[your U<1 rule here]))-6.6667)

Multiple IF Function
 

Awesome!! Now I know the format, but when I try to put my U<1 rule in, I get
an error. I need to show Months occuring between two dates over a year
apart. In other words, I need to multiply the number of months the employee
has worked (less the current month) x 6.6667 hours.

Can you please advise? Thanks!!

"smartin" wrote:

Teri wrote:
I am trying to calculate accrued vacation. Our employees accrue 6.667
hours/month. For someone who has more than one year of service, I have the
following formula:

=(IF(U11,MONTH(E8)*6.667,U1*6.667)-6.6667)

U1 = Years of service
E8 = TODAY()

I need to add a nested function to the above which would calculate the
accrual if Cell U1 is <1. Can anybody help?

You are already handling U<=1 with
U1*6.667

So I presume you want that to apply to U=1 only and do something
different for U<1:

=(IF(U11,MONTH(E8)*6.667,IF(U=1,U1*6.667,[your U<1 rule here]))-6.6667)

Multiple IF Function
 

Hi Teri, please explain what is represented in columns U and E. I can't
see clear to a solution yet.


Teri wrote:
Awesome!! Now I know the format, but when I try to put my U<1 rule in, I get
an error. I need to show Months occuring between two dates over a year
apart. In other words, I need to multiply the number of months the employee
has worked (less the current month) x 6.6667 hours.

Can you please advise? Thanks!!

"smartin" wrote:

Teri wrote:
I am trying to calculate accrued vacation. Our employees accrue 6.667
hours/month. For someone who has more than one year of service, I have the
following formula:

=(IF(U11,MONTH(E8)*6.667,U1*6.667)-6.6667)

U1 = Years of service
E8 = TODAY()

I need to add a nested function to the above which would calculate the
accrual if Cell U1 is <1. Can anybody help?
You are already handling U<=1 with
U1*6.667

So I presume you want that to apply to U=1 only and do something
different for U<1:

=(IF(U11,MONTH(E8)*6.667,IF(U=1,U1*6.667,[your U<1 rule here]))-6.6667)

Multiple IF Function
 

U1 = Years of Service: =(NOW()-$J$2)/365 $J$2 = Date of Hire
E8 = TODAY()

Thank you so much for responding!

"smartin" wrote:

Hi Teri, please explain what is represented in columns U and E. I can't
see clear to a solution yet.


Teri wrote:
Awesome!! Now I know the format, but when I try to put my U<1 rule in, I get
an error. I need to show Months occuring between two dates over a year
apart. In other words, I need to multiply the number of months the employee
has worked (less the current month) x 6.6667 hours.

Can you please advise? Thanks!!

"smartin" wrote:

Teri wrote:
I am trying to calculate accrued vacation. Our employees accrue 6.667
hours/month. For someone who has more than one year of service, I have the
following formula:

=(IF(U11,MONTH(E8)*6.667,U1*6.667)-6.6667)

U1 = Years of service
E8 = TODAY()

I need to add a nested function to the above which would calculate the
accrual if Cell U1 is <1. Can anybody help?
You are already handling U<=1 with
U1*6.667

So I presume you want that to apply to U=1 only and do something
different for U<1:

=(IF(U11,MONTH(E8)*6.667,IF(U=1,U1*6.667,[your U<1 rule here]))-6.6667)

Multiple IF Function
 

Rather than =(NOW()-$J$2)/365 you may be better with
=DATEDIF($J$2,TODAY(),"y")

You may also wish to use DATEDIF for the number of months worked.
--
David Biddulph

"Teri" wrote in message
...
U1 = Years of Service: =(NOW()-$J$2)/365 $J$2 = Date of Hire
E8 = TODAY()

Thank you so much for responding!

"smartin" wrote:

Hi Teri, please explain what is represented in columns U and E. I can't
see clear to a solution yet.


Teri wrote:
Awesome!! Now I know the format, but when I try to put my U<1 rule in,
I get
an error. I need to show Months occuring between two dates over a year
apart. In other words, I need to multiply the number of months the
employee
has worked (less the current month) x 6.6667 hours.

Can you please advise? Thanks!!

"smartin" wrote:

Teri wrote:
I am trying to calculate accrued vacation. Our employees accrue
6.667
hours/month. For someone who has more than one year of service, I
have the
following formula:

=(IF(U11,MONTH(E8)*6.667,U1*6.667)-6.6667)

U1 = Years of service
E8 = TODAY()

I need to add a nested function to the above which would calculate
the
accrual if Cell U1 is <1. Can anybody help?
You are already handling U<=1 with
U1*6.667

So I presume you want that to apply to U=1 only and do something
different for U<1:

=(IF(U11,MONTH(E8)*6.667,IF(U=1,U1*6.667,[your U<1 rule
here]))-6.6667)

Multiple IF Function
 

Thanks for the tip David :) Any help on how to next the function from
'smartin'?

"David Biddulph" wrote:

Rather than =(NOW()-$J$2)/365 you may be better with
=DATEDIF($J$2,TODAY(),"y")

You may also wish to use DATEDIF for the number of months worked.
--
David Biddulph

"Teri" wrote in message
...
U1 = Years of Service: =(NOW()-$J$2)/365 $J$2 = Date of Hire
E8 = TODAY()

Thank you so much for responding!

"smartin" wrote:

Hi Teri, please explain what is represented in columns U and E. I can't
see clear to a solution yet.


Teri wrote:
Awesome!! Now I know the format, but when I try to put my U<1 rule in,
I get
an error. I need to show Months occuring between two dates over a year
apart. In other words, I need to multiply the number of months the
employee
has worked (less the current month) x 6.6667 hours.

Can you please advise? Thanks!!

"smartin" wrote:

Teri wrote:
I am trying to calculate accrued vacation. Our employees accrue
6.667
hours/month. For someone who has more than one year of service, I
have the
following formula:

=(IF(U11,MONTH(E8)*6.667,U1*6.667)-6.6667)

U1 = Years of service
E8 = TODAY()

I need to add a nested function to the above which would calculate
the
accrual if Cell U1 is <1. Can anybody help?
You are already handling U<=1 with
U1*6.667

So I presume you want that to apply to U=1 only and do something
different for U<1:

=(IF(U11,MONTH(E8)*6.667,IF(U=1,U1*6.667,[your U<1 rule
here]))-6.6667)