Date Calc. HELP

  • Thread starter Thread starter Jerry Kinder
  • Start date Start date
J

Jerry Kinder

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,DATE(2005,3,31)),"")

Format the cell as DATE

Biff
 
Ooops!
and if the H37+3 is in April and not in March I want nothing displayed also
Click to expand...

Try this in that case:

=IF(AND(MONTH(H37)=3,YEAR(H37)=2005),IF(MONTH(H37+3)>3,"",H37+3),"")

Biff
 
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
 
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
 
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
 
It works for me too!

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Biff,

I finally got it to work, just needed to understand it.
THANKS, so for your help.

JK
 
Back
Top