Tyro said: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 said: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.
dballou said: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 said: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 said: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.
Fred Smith said: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 said: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 said: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
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.
Dana DeLouis said:Just another idea, if the Max is 80 Hours...
=MOD(1441408, 8*MIN(6,DATEDIF(HIreDate,TODAY(),"y")) + 64)
--
HTH :>)
Dana DeLouis
Fred Smith said: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 said: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.
:
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
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.
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.