End Of Month Formula

M

Mathew P Bennett

Good evening all.
I am a little stumped here, cannot seem to resolve this simple requirement.

In cell A1 I have the value from 1 to 12 (representing the month). (This is
manually entered.)
Now, in cell B1, I would like to enter a formula, so that the last date of
the month in A1 is shown, eg


A B
1 5 30/05/03 .... or

A B
1 12 31/12/03 ... etc


As usual any assistance would be grayefully received.
Cheers,
Mathew
 
H

Henry

Mathew,

The last day of the month is ALWAYS the day before the first of the next
month.
So, if you tell excel that you want the 0 Feb 2003 then it will come back
with 31 Jan 2003
So B1 = DATE(2003, A1 +1,0)
(you may have to format B1 to Date format)

HTH
Henry
 
F

fil007

In cell A1 I have the value from 1 to 12 (representing the month). (This is
manually entered.)
Now, in cell B1, I would like to enter a formula, so that the last date of
the month in A1 is shown, eg


A B
1 5 30/05/03 .... or
Click to expand...



Are you sure ??? I think it's 31/05/03


A B
1 12 31/12/03 ... etc


As usual any assistance would be grayefully received.
Cheers,
Mathew
Click to expand...


There is in french a function name date (it can be the same name in english)

this function wait for 3 parameters like that =date(Year;Month;Day) i'm not
sure of the order ....


you can use this formula in the B1 cells:
=date(year(today());A1+1;1)-1

it's the day before the first day of the next month :)

FIL
 
M

Mathew P Bennett

Hi Henry,
Thank you very much, it works well, just as I asked.
I have one further amendment which I thought I could add on to your
solution, but as before am stumped.
I now also wish that B1 shows the end of the month, 4 months on from that
shown in A1.
(The reason being that if I have 5 in A1, ( logically you would think this
is month 5 ie May, however my financial year starts April 01, and hence 5
actually represents the month of August.)
Any further suggestions?

As always,
yours,
Mathew

PS. I would also like to thank fil007 & gromit for your suggestions on the
same lines.
 
M

Mathew P Bennett

Hi Again Henry

Got it now, after a little logical thinking

=DATE(2003,A1+4,0)
Cheers again
Mathew
 
Top