If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
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. |
Thread Tools | |
Display Modes | |
|
|