multiple If's based on today's date
 

I need to have the value of a cell based on the number of days past from a
date in another cell. i.e. A1 = 40 if A2 [date] is <=today()+365 or, A1=80
if A2 is =today+365 but <=1460. The idea is anual vacation paid based on
hire date.

multiple If's based on today's date
 

Your statement is unclear to me, but I'm assuming your want an answer in A1
of 40 or 80 and thus
A1: =IF(A2<=TODAY()+365,40,IF(AND(A2=TODAY()+365,A2<= TODAY()+1460),80,0))
You didn't state what to do if the conditions are not met, so I return 0.
Also you have a conflict that if A2 is = TODAY()+365, it appears you want
both 40 and 80 as an answer in A1..
If you can better define your requirements, a solution can easily be found.

Tyro

"dballou" wrote in message
...
I need to have the value of a cell based on the number of days past from a
date in another cell. i.e. A1 = 40 if A2 [date] is <=today()+365 or,
A1=80
if A2 is =today+365 but <=1460. The idea is anual vacation paid based on
hire date.

multiple If's based on today's date
 

Thanks Tyro
What I'm trying to do is display the number of vacation hours available to a
person based on thier date of hire. They earn 40 hours per year the first
year, 48 the second year and so on up to 80 hours after at 5 years. I was
trying to use the 40 and 80 as examples but will need to figure all scenarios
in the formula to make it work.

"Tyro" wrote:

Your statement is unclear to me, but I'm assuming your want an answer in A1
of 40 or 80 and thus
A1: =IF(A2<=TODAY()+365,40,IF(AND(A2=TODAY()+365,A2<= TODAY()+1460),80,0))
You didn't state what to do if the conditions are not met, so I return 0.
Also you have a conflict that if A2 is = TODAY()+365, it appears you want
both 40 and 80 as an answer in A1..
If you can better define your requirements, a solution can easily be found.

Tyro

"dballou" wrote in message
...
I need to have the value of a cell based on the number of days past from a
date in another cell. i.e. A1 = 40 if A2 [date] is <=today()+365 or,
A1=80
if A2 is =today+365 but <=1460. The idea is anual vacation paid based on
hire date.

multiple If's based on today's date
 

So it's 40 hours after 1 year plus 8 hours per year for a maximum of 5
years, right? If so, try:

=IF(DATEDIF(A2,TODAY(),"Y")<1,0,40+MIN(DATEDIF(A2, TODAY(),"Y")-1,5)*8)

Using Datedif, rather than 365 days per year will allow you to handle leap
years properly.

As always, it's best to post your specific problem up front, rather than a
hypothetical question.

Regards
Fred.


"dballou" wrote in message
...
Thanks Tyro
What I'm trying to do is display the number of vacation hours available to
a
person based on thier date of hire. They earn 40 hours per year the first
year, 48 the second year and so on up to 80 hours after at 5 years. I was
trying to use the 40 and 80 as examples but will need to figure all
scenarios
in the formula to make it work.

"Tyro" wrote:

Your statement is unclear to me, but I'm assuming your want an answer in
A1
of 40 or 80 and thus
A1:
=IF(A2<=TODAY()+365,40,IF(AND(A2=TODAY()+365,A2<= TODAY()+1460),80,0))
You didn't state what to do if the conditions are not met, so I return 0.
Also you have a conflict that if A2 is = TODAY()+365, it appears you want
both 40 and 80 as an answer in A1..
If you can better define your requirements, a solution can easily be
found.

Tyro

"dballou" wrote in message
...
I need to have the value of a cell based on the number of days past from
a
date in another cell. i.e. A1 = 40 if A2 [date] is <=today()+365 or,
A1=80
if A2 is =today+365 but <=1460. The idea is anual vacation paid based
on
hire date.

multiple If's based on today's date
 

Just another idea, if the Max is 80 Hours...

=MOD(1441408, 8*MIN(6,DATEDIF(HIreDate,TODAY(),"y")) + 64)

--
HTH :)
Dana DeLouis


"Fred Smith" wrote in message
...
So it's 40 hours after 1 year plus 8 hours per year for a maximum of 5
years, right? If so, try:

=IF(DATEDIF(A2,TODAY(),"Y")<1,0,40+MIN(DATEDIF(A2, TODAY(),"Y")-1,5)*8)

Using Datedif, rather than 365 days per year will allow you to handle leap
years properly.

As always, it's best to post your specific problem up front, rather than a
hypothetical question.

Regards
Fred.


"dballou" wrote in message
...
Thanks Tyro
What I'm trying to do is display the number of vacation hours available
to a
person based on thier date of hire. They earn 40 hours per year the
first
year, 48 the second year and so on up to 80 hours after at 5 years. I
was
trying to use the 40 and 80 as examples but will need to figure all
scenarios
in the formula to make it work.

"Tyro" wrote:

Your statement is unclear to me, but I'm assuming your want an answer in
A1
of 40 or 80 and thus
A1:
=IF(A2<=TODAY()+365,40,IF(AND(A2=TODAY()+365,A2<= TODAY()+1460),80,0))
You didn't state what to do if the conditions are not met, so I return
0.
Also you have a conflict that if A2 is = TODAY()+365, it appears you
want
both 40 and 80 as an answer in A1..
If you can better define your requirements, a solution can easily be
found.

Tyro

"dballou" wrote in message
...
I need to have the value of a cell based on the number of days past
from a
date in another cell. i.e. A1 = 40 if A2 [date] is <=today()+365 or,
A1=80
if A2 is =today+365 but <=1460. The idea is anual vacation paid
based on
hire date.

multiple If's based on today's date
 

Thanks to all!

"Dana DeLouis" wrote:

Just another idea, if the Max is 80 Hours...

=MOD(1441408, 8*MIN(6,DATEDIF(HIreDate,TODAY(),"y")) + 64)

--
HTH :)
Dana DeLouis


"Fred Smith" wrote in message
...
So it's 40 hours after 1 year plus 8 hours per year for a maximum of 5
years, right? If so, try:

=IF(DATEDIF(A2,TODAY(),"Y")<1,0,40+MIN(DATEDIF(A2, TODAY(),"Y")-1,5)*8)

Using Datedif, rather than 365 days per year will allow you to handle leap
years properly.

As always, it's best to post your specific problem up front, rather than a
hypothetical question.

Regards
Fred.


"dballou" wrote in message
...
Thanks Tyro
What I'm trying to do is display the number of vacation hours available
to a
person based on thier date of hire. They earn 40 hours per year the
first
year, 48 the second year and so on up to 80 hours after at 5 years. I
was
trying to use the 40 and 80 as examples but will need to figure all
scenarios
in the formula to make it work.

"Tyro" wrote:

Your statement is unclear to me, but I'm assuming your want an answer in
A1
of 40 or 80 and thus
A1:
=IF(A2<=TODAY()+365,40,IF(AND(A2=TODAY()+365,A2<= TODAY()+1460),80,0))
You didn't state what to do if the conditions are not met, so I return
0.
Also you have a conflict that if A2 is = TODAY()+365, it appears you
want
both 40 and 80 as an answer in A1..
If you can better define your requirements, a solution can easily be
found.

Tyro

"dballou" wrote in message
...
I need to have the value of a cell based on the number of days past
from a
date in another cell. i.e. A1 = 40 if A2 [date] is <=today()+365 or,
A1=80
if A2 is =today+365 but <=1460. The idea is anual vacation paid
based on
hire date.