End of month calculations

[Guest]

I have the following formula that gives me the date at the end of the month
six month on. eg if I put a date of 15 Aug 06 in cell A1, the result in A2
would be 28 Feb 07. What I need also if the date in A1 is a month early (say
28 Jul 06), the result in A2 still needs to be 28 Feb 07. I hope I made it
clear as mud!!

=EOMONTH(A1,6)

 

[Leo Heuser]

I have the following formula that gives me the date at the end of the month
six month on. eg if I put a date of 15 Aug 06 in cell A1, the result in A2
would be 28 Feb 07. What I need also if the date in A1 is a month early
(say
28 Jul 06), the result in A2 still needs to be 28 Feb 07. I hope I made it
clear as mud!!

=EOMONTH(A1,6)

A month early. Compared to what?
If all Excel has is A1, how is it going to "know", what
a month early is?

 

[Guest]

A month earlier to the date in cell A2.
--
ASU

A month early. Compared to what?
If all Excel has is A1, how is it going to "know", what
a month early is?

--
Best regards
Leo Heuser

Followup to newsgroup only please.

 

[Guest]

if A1 is 27th July 2006 would A2 be 31 Jan 07?

If so perhaps try

=EOMONTH(A1,6+(DAY(EOMONTH(A1,7))<=DAY(A1)))

If not then please define date ranges for A1 that would give 31 Jan 07 in A2
or 31 Mar 07 in A2

 

[Guest]

Daddylonglegs, Thanks very much, just the job it works fine.