End Of Month Formula

  • Thread starter Thread starter Mathew P Bennett
  • Start date Start date
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
 
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
 
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
 
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.
 
Hi Again Henry

Got it now, after a little logical thinking

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