| Home |
| Search |
| Today's Posts |
|
|
|
#1
|
|||
|
|||
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 |
|
#2
|
|||
|
|||
|
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 |
|
#3
|
|||
|
|||
|
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 |
|
#4
|
|||
|
|||
|
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 |
|
#5
|
|||
|
|||
|
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 |
|
#6
|
|||
|
|||
|
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 |
|
#7
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| extract date from the most current date | Excel Discussion (Misc queries) | |||
| Date Math Problem | Excel Worksheet Functions | |||
| Use a calc to figure sum in months using date fields but numeric r | Excel Worksheet Functions | |||
| Addition to Turn cell red if today is greater or equal to date in cell | New Users to Excel | |||
| calc constant date from variable date & return with ability to rn. | Excel Worksheet Functions | |||
Hybrid Mode
