|
Date Calc. HELP
Hi,
My formula: IF($H$37<MONTH(3)*YEAR(2005),"",$H$37+3) IF H37 is less than or grater than March 2005 (not in March) then, display nothing, elae (if it is in March)display the date+3. I want it to display a date in March only not in any other month. H37 has March 26, 2005 in it. I want the formula cell to display March 29th. If H37 has no date I want the formula cell to display nothing and if the H37+3 is in April and not in March I want nothing displayed also, diaplay only if the +3 is a date in March. What I get with the above is nothing if there is a date in March. When I change the formula to the following I get the correct date but this is wrong: IF($H$37<MONTH(3)*YEAR(2005),$H$37+3,"") SO what have I done?? What should I do! Thank you, JK |
Hi!
Try this: =IF(AND(MONTH(H37)=3,YEAR(H37)=2005),MIN(H37+3,DAT E(2005,3,31)),"") Format the cell as DATE Biff "Jerry Kinder" wrote in message ... Hi, My formula: IF($H$37<MONTH(3)*YEAR(2005),"",$H$37+3) IF H37 is less than or grater than March 2005 (not in March) then, display nothing, elae (if it is in March)display the date+3. I want it to display a date in March only not in any other month. H37 has March 26, 2005 in it. I want the formula cell to display March 29th. If H37 has no date I want the formula cell to display nothing and if the H37+3 is in April and not in March I want nothing displayed also, diaplay only if the +3 is a date in March. What I get with the above is nothing if there is a date in March. When I change the formula to the following I get the correct date but this is wrong: IF($H$37<MONTH(3)*YEAR(2005),$H$37+3,"") SO what have I done?? What should I do! Thank you, JK |
Ooops!
and if the H37+3 is in April and not in March I want nothing displayed also Try this in that case: =IF(AND(MONTH(H37)=3,YEAR(H37)=2005),IF(MONTH(H37+ 3)3,"",H37+3),"") Biff "Biff" wrote in message ... Hi! Try this: =IF(AND(MONTH(H37)=3,YEAR(H37)=2005),MIN(H37+3,DAT E(2005,3,31)),"") Format the cell as DATE Biff "Jerry Kinder" wrote in message ... Hi, My formula: IF($H$37<MONTH(3)*YEAR(2005),"",$H$37+3) IF H37 is less than or grater than March 2005 (not in March) then, display nothing, elae (if it is in March)display the date+3. I want it to display a date in March only not in any other month. H37 has March 26, 2005 in it. I want the formula cell to display March 29th. If H37 has no date I want the formula cell to display nothing and if the H37+3 is in April and not in March I want nothing displayed also, diaplay only if the +3 is a date in March. What I get with the above is nothing if there is a date in March. When I change the formula to the following I get the correct date but this is wrong: IF($H$37<MONTH(3)*YEAR(2005),$H$37+3,"") SO what have I done?? What should I do! Thank you, JK |
Hi Biff,
Thanks, I tried the formula and it works but it still diplays april dates. I do not understand the ,IF(MONTH(h37+3)3, section? Does this mean add 3 months to the month in H37, March + 3 ?? and what does the 3 do? Sorry I do not understand but it is the learning curve thing. Thanks, JK "Biff" wrote in message ... Ooops! and if the H37+3 is in April and not in March I want nothing displayed also Try this in that case: =IF(AND(MONTH(H37)=3,YEAR(H37)=2005),IF(MONTH(H37+ 3)3,"",H37+3),"") Biff "Biff" wrote in message ... Hi! Try this: =IF(AND(MONTH(H37)=3,YEAR(H37)=2005),MIN(H37+3,DAT E(2005,3,31)),"") Format the cell as DATE Biff "Jerry Kinder" wrote in message ... Hi, My formula: IF($H$37<MONTH(3)*YEAR(2005),"",$H$37+3) IF H37 is less than or grater than March 2005 (not in March) then, display nothing, elae (if it is in March)display the date+3. I want it to display a date in March only not in any other month. H37 has March 26, 2005 in it. I want the formula cell to display March 29th. If H37 has no date I want the formula cell to display nothing and if the H37+3 is in April and not in March I want nothing displayed also, diaplay only if the +3 is a date in March. What I get with the above is nothing if there is a date in March. When I change the formula to the following I get the correct date but this is wrong: IF($H$37<MONTH(3)*YEAR(2005),$H$37+3,"") SO what have I done?? What should I do! Thank you, JK |
The formula works correctly.
IF(MONTH(h37+3)3 here, you add 3 days to the date in h37 and then get the month of this new date. This is then checked if its is greater than 3 (which stands for march). If the result is a date in april then it is not displayed for the case where the result is 1 or 2 april. Mangesh "Jerry Kinder" wrote in message ... Hi Biff, Thanks, I tried the formula and it works but it still diplays april dates. I do not understand the ,IF(MONTH(h37+3)3, section? Does this mean add 3 months to the month in H37, March + 3 ?? and what does the 3 do? Sorry I do not understand but it is the learning curve thing. Thanks, JK "Biff" wrote in message ... Ooops! and if the H37+3 is in April and not in March I want nothing displayed also Try this in that case: =IF(AND(MONTH(H37)=3,YEAR(H37)=2005),IF(MONTH(H37+ 3)3,"",H37+3),"") Biff "Biff" wrote in message ... Hi! Try this: =IF(AND(MONTH(H37)=3,YEAR(H37)=2005),MIN(H37+3,DAT E(2005,3,31)),"") Format the cell as DATE Biff "Jerry Kinder" wrote in message ... Hi, My formula: IF($H$37<MONTH(3)*YEAR(2005),"",$H$37+3) IF H37 is less than or grater than March 2005 (not in March) then, display nothing, elae (if it is in March)display the date+3. I want it to display a date in March only not in any other month. H37 has March 26, 2005 in it. I want the formula cell to display March 29th. If H37 has no date I want the formula cell to display nothing and if the H37+3 is in April and not in March I want nothing displayed also, diaplay only if the +3 is a date in March. What I get with the above is nothing if there is a date in March. When I change the formula to the following I get the correct date but this is wrong: IF($H$37<MONTH(3)*YEAR(2005),$H$37+3,"") SO what have I done?? What should I do! Thank you, JK |
Hi,
I know I am doing something wrong but using this formula in A14 =IF(AND(MONTH(H37)=3,YEAR(H37)=2005),IF(MONTH(H37+ 3)3,"",H37+3),"") I still get April 1 if H37 is the last day of march, if not the last day of march I get the correct date. Jerry "Mangesh Yadav" wrote in message ... The formula works correctly. IF(MONTH(h37+3)3 here, you add 3 days to the date in h37 and then get the month of this new date. This is then checked if its is greater than 3 (which stands for march). If the result is a date in april then it is not displayed for the case where the result is 1 or 2 april. Mangesh "Jerry Kinder" wrote in message ... Hi Biff, Thanks, I tried the formula and it works but it still diplays april dates. I do not understand the ,IF(MONTH(h37+3)3, section? Does this mean add 3 months to the month in H37, March + 3 ?? and what does the 3 do? Sorry I do not understand but it is the learning curve thing. Thanks, JK "Biff" wrote in message ... Ooops! and if the H37+3 is in April and not in March I want nothing displayed also Try this in that case: =IF(AND(MONTH(H37)=3,YEAR(H37)=2005),IF(MONTH(H37+ 3)3,"",H37+3),"") Biff "Biff" wrote in message ... Hi! Try this: =IF(AND(MONTH(H37)=3,YEAR(H37)=2005),MIN(H37+3,DAT E(2005,3,31)),"") Format the cell as DATE Biff "Jerry Kinder" wrote in message ... Hi, My formula: IF($H$37<MONTH(3)*YEAR(2005),"",$H$37+3) IF H37 is less than or grater than March 2005 (not in March) then, display nothing, elae (if it is in March)display the date+3. I want it to display a date in March only not in any other month. H37 has March 26, 2005 in it. I want the formula cell to display March 29th. If H37 has no date I want the formula cell to display nothing and if the H37+3 is in April and not in March I want nothing displayed also, diaplay only if the +3 is a date in March. What I get with the above is nothing if there is a date in March. When I change the formula to the following I get the correct date but this is wrong: IF($H$37<MONTH(3)*YEAR(2005),$H$37+3,"") SO what have I done?? What should I do! Thank you, JK |
It works for me too!
-- HTH RP (remove nothere from the email address if mailing direct) "Jerry Kinder" wrote in message ... Hi, I know I am doing something wrong but using this formula in A14 =IF(AND(MONTH(H37)=3,YEAR(H37)=2005),IF(MONTH(H37+ 3)3,"",H37+3),"") I still get April 1 if H37 is the last day of march, if not the last day of march I get the correct date. Jerry "Mangesh Yadav" wrote in message ... The formula works correctly. IF(MONTH(h37+3)3 here, you add 3 days to the date in h37 and then get the month of this new date. This is then checked if its is greater than 3 (which stands for march). If the result is a date in april then it is not displayed for the case where the result is 1 or 2 april. Mangesh "Jerry Kinder" wrote in message ... Hi Biff, Thanks, I tried the formula and it works but it still diplays april dates. I do not understand the ,IF(MONTH(h37+3)3, section? Does this mean add 3 months to the month in H37, March + 3 ?? and what does the 3 do? Sorry I do not understand but it is the learning curve thing. Thanks, JK "Biff" wrote in message ... Ooops! and if the H37+3 is in April and not in March I want nothing displayed also Try this in that case: =IF(AND(MONTH(H37)=3,YEAR(H37)=2005),IF(MONTH(H37+ 3)3,"",H37+3),"") Biff "Biff" wrote in message ... Hi! Try this: =IF(AND(MONTH(H37)=3,YEAR(H37)=2005),MIN(H37+3,DAT E(2005,3,31)),"") Format the cell as DATE Biff "Jerry Kinder" wrote in message ... Hi, My formula: IF($H$37<MONTH(3)*YEAR(2005),"",$H$37+3) IF H37 is less than or grater than March 2005 (not in March) then, display nothing, elae (if it is in March)display the date+3. I want it to display a date in March only not in any other month. H37 has March 26, 2005 in it. I want the formula cell to display March 29th. If H37 has no date I want the formula cell to display nothing and if the H37+3 is in April and not in March I want nothing displayed also, diaplay only if the +3 is a date in March. What I get with the above is nothing if there is a date in March. When I change the formula to the following I get the correct date but this is wrong: IF($H$37<MONTH(3)*YEAR(2005),$H$37+3,"") SO what have I done?? What should I do! Thank you, JK |
Biff,
I finally got it to work, just needed to understand it. THANKS, so for your help. JK "Biff" wrote in message ... Ooops! and if the H37+3 is in April and not in March I want nothing displayed also Try this in that case: =IF(AND(MONTH(H37)=3,YEAR(H37)=2005),IF(MONTH(H37+ 3)3,"",H37+3),"") Biff "Biff" wrote in message ... Hi! Try this: =IF(AND(MONTH(H37)=3,YEAR(H37)=2005),MIN(H37+3,DAT E(2005,3,31)),"") Format the cell as DATE Biff "Jerry Kinder" wrote in message ... Hi, My formula: IF($H$37<MONTH(3)*YEAR(2005),"",$H$37+3) IF H37 is less than or grater than March 2005 (not in March) then, display nothing, elae (if it is in March)display the date+3. I want it to display a date in March only not in any other month. H37 has March 26, 2005 in it. I want the formula cell to display March 29th. If H37 has no date I want the formula cell to display nothing and if the H37+3 is in April and not in March I want nothing displayed also, diaplay only if the +3 is a date in March. What I get with the above is nothing if there is a date in March. When I change the formula to the following I get the correct date but this is wrong: IF($H$37<MONTH(3)*YEAR(2005),$H$37+3,"") SO what have I done?? What should I do! Thank you, JK |
| All times are GMT +1. The time now is 07:31 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com