Months

R

Ryan Sheepwash

You have cell for each month of the year. But the first cell is set for the
"todays" date. say 'A1' is todays date, 'A2' would be the next month
following.. So I put 'A1+30' meaning todays date plus 30 days in a month. But
the problem I am having is that not all months have 30 days in it some have
31 and february has 28. So for example if I opened the spreadsheet on the
first of the July. I would get a result of July Two times. Because July 1
plus 30 days is July 31. If anyone has a formula that is workable for my
problem please let me know.
 
P

Paul B

Ryan, try this,

=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003
 
R

Ron Rosenfeld

On Tue, 11 Oct 2005 10:12:01 -0700, "Ryan Sheepwash" <Ryan
You have cell for each month of the year. But the first cell is set for the
"todays" date. say 'A1' is todays date, 'A2' would be the next month
following.. So I put 'A1+30' meaning todays date plus 30 days in a month. But
the problem I am having is that not all months have 30 days in it some have
31 and february has 28. So for example if I opened the spreadsheet on the
first of the July. I would get a result of July Two times. Because July 1
plus 30 days is July 31. If anyone has a formula that is workable for my
problem please let me know.
Click to expand...

Ryan,

Well, the question that you have to answer is what date do you wish to show
when you TODAY is 31 Jan 2005?

?? 28 Feb 2005
?? 1 Mar 2005
?? 2 Mar 2005
?? 3 Mar 2005

How about when TODAY is 28 Feb 2005

?? 28 Mar 2005
?? 29 Mar 2005
?? ... etc

Assuming that you want 28 Feb 2005 as your result in the first instance, then
look at the EDATE function. =EDATE(A1,1) will adjust for those pesky months
that don't all have the same numbers of days.

You do have to have the Analysis Tool Pack installed (how to do it is in HELP).


--ron
 
B

Bob Phillips

You could try

=DATE(YEAR(B1),MONTH(B1)+1,MIN(DAY(B1),DAY(DATE(YEAR(B1),MONTH(B1)+2,0))))

but the problem is that it moves 31st Jan to 28th Feb ok, but then it
becomes 28th March, etc. A knotty problem.
 
G

Guest

zz
Bob Phillips said:
You could try

=DATE(YEAR(B1),MONTH(B1)+1,MIN(DAY(B1),DAY(DATE(YEAR(B1),MONTH(B1)+2,0))))

but the problem is that it moves 31st Jan to 28th Feb ok, but then it
becomes 28th March, etc. A knotty problem.

--
HTH

Bob Phillips

month.
Click to expand...
 
R

Ron Rosenfeld

You could try

=DATE(YEAR(B1),MONTH(B1)+1,MIN(DAY(B1),DAY(DATE(YEAR(B1),MONTH(B1)+2,0))))

but the problem is that it moves 31st Jan to 28th Feb ok, but then it
becomes 28th March, etc. A knotty problem.
Click to expand...

I think the solution to that problem is to always reference back to the index
cell, instead of the preceding cell in the first part of the equation.


So:

=DATE(YEAR($B$1),MONTH($B$1)+ROW()-1,MIN(DAY($B$1),DAY(DATE(YEAR($B$1),MONTH($B$1)+ROW(),0))))

or

=DATE(YEAR($B$1),MONTH($B$1)+COUNT($B$1:B1),MIN(DAY($B$1),
DAY(DATE(YEAR($B$1),MONTH($B$1)+COUNT($B$1:B1)+1,0))))



--ron
 
C

Chris Lavender

If you only need the month to show, you could use =EOMONTH(A1,0)+1 and
format the cell as mmmm

Best rgds
Chris Lav
 
Top