| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with formula
My problem is with the first year only! Can't figure out how to write this
formula with certain criteria within the first year. Which is: If B16(hire date)< 90 days then "0" on all curcumstances, AFTER 90 days then If B16(hire date) is between Jan. 1st - June 30th then "3", If between July 1st - Sept 30th then "2". My problem lies when if you have 90 days completed when the next year rolls around you have "3". Then it's "3" every year untll your 5th year, then it's "4", every year after that. Example: Here's what I have now which works fine untill Jan. 1st which should change to "3" instead it stays at "2" untill a whole year is completed. =IF(H4<2.4934,0,IF(DATEDIF(B16,TODAY(),"Y")<5,IF(A ND(DATEDIF(B16,TODAY(),"Y")<1,MONTH(B16)6),2,3),4 )) Thanks in Advance! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with formula
I need it to change to "3" when the year changes to the next year if you have
90+ days the following year and stay at "3" untill the 6th year then change to "4". Otherwise it's 0 or 2: Example: If start date is July 31, 2006 then that person will have 2 days to use by Dec.31,2006 , Once Jan1, 2007 rolls around it changes to "3" even though a whole year has not been completed. "Sandy Mann" wrote: Ritchard, date) is between Jan. 1st - June 30th then "3", If between July 1st - Sept 30th then "2". What happens if the date is after 1st September? I assume you meant 1st July - 31 December 30th then "2". My problem lies when if you have 90 days completed when the next year rolls around you have "3". Then it's "3" every year untll your 5th year, then it's "4" I assume that you want a date after 1st July to remain 2 until it is after 5 years if so then try: =IF(H4<2.4934,0,IF(TODAY()-B16<=90,0,IF(DATEDIF(B16,TODAY(),"Y")<5,IF(MONTH(B 16)6,2,3),4))) -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "Richard" wrote in message ... My problem is with the first year only! Can't figure out how to write this formula with certain criteria within the first year. Which is: If B16(hire date)< 90 days then "0" on all curcumstances, AFTER 90 days then If B16(hire date) is between Jan. 1st - June 30th then "3", If between July 1st - Sept 30th then "2". My problem lies when if you have 90 days completed when the next year rolls around you have "3". Then it's "3" every year untll your 5th year, then it's "4", every year after that. Example: Here's what I have now which works fine untill Jan. 1st which should change to "3" instead it stays at "2" untill a whole year is completed. =IF(H4<2.4934,0,IF(DATEDIF(B16,TODAY(),"Y")<5,IF(A ND(DATEDIF(B16,TODAY(),"Y")<1,MONTH(B16)6),2,3),4 )) Thanks in Advance! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with formula
OK try:
=4-(YEAR(TODAY())-YEAR(B16)<6)-(AND(YEAR(TODAY())=YEAR(B16),MONTH(B16)6)) -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "Richard" wrote in message ... I need it to change to "3" when the year changes to the next year if you have 90+ days the following year and stay at "3" untill the 6th year then change to "4". Otherwise it's 0 or 2: Example: If start date is July 31, 2006 then that person will have 2 days to use by Dec.31,2006 , Once Jan1, 2007 rolls around it changes to "3" even though a whole year has not been completed. "Sandy Mann" wrote: Ritchard, date) is between Jan. 1st - June 30th then "3", If between July 1st - Sept 30th then "2". What happens if the date is after 1st September? I assume you meant 1st July - 31 December 30th then "2". My problem lies when if you have 90 days completed when the next year rolls around you have "3". Then it's "3" every year untll your 5th year, then it's "4" I assume that you want a date after 1st July to remain 2 until it is after 5 years if so then try: =IF(H4<2.4934,0,IF(TODAY()-B16<=90,0,IF(DATEDIF(B16,TODAY(),"Y")<5,IF(MONTH(B 16)6,2,3),4))) -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "Richard" wrote in message ... My problem is with the first year only! Can't figure out how to write this formula with certain criteria within the first year. Which is: If B16(hire date)< 90 days then "0" on all curcumstances, AFTER 90 days then If B16(hire date) is between Jan. 1st - June 30th then "3", If between July 1st - Sept 30th then "2". My problem lies when if you have 90 days completed when the next year rolls around you have "3". Then it's "3" every year untll your 5th year, then it's "4", every year after that. Example: Here's what I have now which works fine untill Jan. 1st which should change to "3" instead it stays at "2" untill a whole year is completed. =IF(H4<2.4934,0,IF(DATEDIF(B16,TODAY(),"Y")<5,IF(A ND(DATEDIF(B16,TODAY(),"Y")<1,MONTH(B16)6),2,3),4 )) Thanks in Advance! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with formula
Forgot the first two conditions make that:
=(4-(YEAR(TODAY())-YEAR(B16)<6)-(AND(YEAR(TODAY())=YEAR(B16),MONTH(B16)6)))*(AND( H42.4934,TODAY()-B1690)) -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "Sandy Mann" wrote in message ... OK try: =4-(YEAR(TODAY())-YEAR(B16)<6)-(AND(YEAR(TODAY())=YEAR(B16),MONTH(B16)6)) -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "Richard" wrote in message ... I need it to change to "3" when the year changes to the next year if you have 90+ days the following year and stay at "3" untill the 6th year then change to "4". Otherwise it's 0 or 2: Example: If start date is July 31, 2006 then that person will have 2 days to use by Dec.31,2006 , Once Jan1, 2007 rolls around it changes to "3" even though a whole year has not been completed. "Sandy Mann" wrote: Ritchard, date) is between Jan. 1st - June 30th then "3", If between July 1st - Sept 30th then "2". What happens if the date is after 1st September? I assume you meant 1st July - 31 December 30th then "2". My problem lies when if you have 90 days completed when the next year rolls around you have "3". Then it's "3" every year untll your 5th year, then it's "4" I assume that you want a date after 1st July to remain 2 until it is after 5 years if so then try: =IF(H4<2.4934,0,IF(TODAY()-B16<=90,0,IF(DATEDIF(B16,TODAY(),"Y")<5,IF(MONTH(B 16)6,2,3),4))) -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "Richard" wrote in message ... My problem is with the first year only! Can't figure out how to write this formula with certain criteria within the first year. Which is: If B16(hire date)< 90 days then "0" on all curcumstances, AFTER 90 days then If B16(hire date) is between Jan. 1st - June 30th then "3", If between July 1st - Sept 30th then "2". My problem lies when if you have 90 days completed when the next year rolls around you have "3". Then it's "3" every year untll your 5th year, then it's "4", every year after that. Example: Here's what I have now which works fine untill Jan. 1st which should change to "3" instead it stays at "2" untill a whole year is completed. =IF(H4<2.4934,0,IF(DATEDIF(B16,TODAY(),"Y")<5,IF(A ND(DATEDIF(B16,TODAY(),"Y")<1,MONTH(B16)6),2,3),4 )) Thanks in Advance! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with formula
Yes that got it! You are amazing!!! I've worked for months on this and no one
at work even came close. Thanks again "Sandy Mann" wrote: Forgot the first two conditions make that: =(4-(YEAR(TODAY())-YEAR(B16)<6)-(AND(YEAR(TODAY())=YEAR(B16),MONTH(B16)6)))*(AND( H42.4934,TODAY()-B1690)) -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "Sandy Mann" wrote in message ... OK try: =4-(YEAR(TODAY())-YEAR(B16)<6)-(AND(YEAR(TODAY())=YEAR(B16),MONTH(B16)6)) -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "Richard" wrote in message ... I need it to change to "3" when the year changes to the next year if you have 90+ days the following year and stay at "3" untill the 6th year then change to "4". Otherwise it's 0 or 2: Example: If start date is July 31, 2006 then that person will have 2 days to use by Dec.31,2006 , Once Jan1, 2007 rolls around it changes to "3" even though a whole year has not been completed. "Sandy Mann" wrote: Ritchard, date) is between Jan. 1st - June 30th then "3", If between July 1st - Sept 30th then "2". What happens if the date is after 1st September? I assume you meant 1st July - 31 December 30th then "2". My problem lies when if you have 90 days completed when the next year rolls around you have "3". Then it's "3" every year untll your 5th year, then it's "4" I assume that you want a date after 1st July to remain 2 until it is after 5 years if so then try: =IF(H4<2.4934,0,IF(TODAY()-B16<=90,0,IF(DATEDIF(B16,TODAY(),"Y")<5,IF(MONTH(B 16)6,2,3),4))) -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "Richard" wrote in message ... My problem is with the first year only! Can't figure out how to write this formula with certain criteria within the first year. Which is: If B16(hire date)< 90 days then "0" on all curcumstances, AFTER 90 days then If B16(hire date) is between Jan. 1st - June 30th then "3", If between July 1st - Sept 30th then "2". My problem lies when if you have 90 days completed when the next year rolls around you have "3". Then it's "3" every year untll your 5th year, then it's "4", every year after that. Example: Here's what I have now which works fine untill Jan. 1st which should change to "3" instead it stays at "2" untill a whole year is completed. =IF(H4<2.4934,0,IF(DATEDIF(B16,TODAY(),"Y")<5,IF(A ND(DATEDIF(B16,TODAY(),"Y")<1,MONTH(B16)6),2,3),4 )) Thanks in Advance! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Match then lookup | Excel Worksheet Functions | |||
| Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
| Creating a check mark box | Setting up and Configuration of Excel | |||
| Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Linear Mode
