Hi All,

In cell A1 I have, say, 30-Nov-08. In cell B1 I want a formula to return
the last day of the last month end i.e. 31-Oct-08. In cell C1 I want a
similar formula to return the last day of the last qaurter end i.e.
30-Sep-08. In cell D1 I want a formula to return the last day of the last
year end i.e. 31-Dec-2007.

Can anyone help?

Many thanks in advance,

KR
Andrew

=DATE(YEAR(A1),MONTH(A1),0)
=DATE(YEAR(A1),3*INT((MONTH(A1)-1)/3)+1,0)
=DATE(YEAR(A1),1,0)
--
David Biddulph

"Andrew Mackenzie" wrote in message
...
Hi All,

In cell A1 I have, say, 30-Nov-08. In cell B1 I want a formula to return
the last day of the last month end i.e. 31-Oct-08. In cell C1 I want a
similar formula to return the last day of the last qaurter end i.e.
30-Sep-08. In cell D1 I want a formula to return the last day of the last
year end i.e. 31-Dec-2007.

Can anyone help?

Many thanks in advance,

KR
Andrew

Brilliant David, Thanks very much for your help.

KR
Andrew

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
=DATE(YEAR(A1),MONTH(A1),0)
=DATE(YEAR(A1),3*INT((MONTH(A1)-1)/3)+1,0)
=DATE(YEAR(A1),1,0)
--
David Biddulph

"Andrew Mackenzie" wrote in message
...
Hi All,

In cell A1 I have, say, 30-Nov-08. In cell B1 I want a formula to
return
the last day of the last month end i.e. 31-Oct-08. In cell C1 I want a
similar formula to return the last day of the last qaurter end i.e.
30-Sep-08. In cell D1 I want a formula to return the last day of the
last
year end i.e. 31-Dec-2007.

Can anyone help?

Many thanks in advance,

KR
Andrew