multiple If's based on today's date

  • Thread starter Thread starter dballou
  • Start date Start date
D

dballou

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.
 
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
 
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.
Click to expand...
Click to expand...
 
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

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.
Click to expand...
Click to expand...
Click to expand...
 
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.

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.
Click to expand...
Click to expand...
Click to expand...
 
Thanks to all!

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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top