| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| conditional formatting for cell date to equal today's date | Excel Worksheet Functions | |||
| how do you conditional format based upon today's date? | Excel Worksheet Functions | |||
| I need today's date returned as date format in formula | Excel Discussion (Misc queries) | |||
| Count number of cells with date <today's date | New Users to Excel | |||
| count the number of cells with a date <= today's date | New Users to Excel | |||
Linear Mode
