Excel challenge

Neill_J

Neill_J

Neill_J

Associate
Joined
18 Oct 2002
Posts
109
Location
Stamford, Lincolnshire
Hi

Anyone know a solution to this one?

Got a spreadsheet which calculates todays date in cell (A2)
formula =TODAY()
(The cells for months are all formatted to display as MMM)

excel.JPG


To calculate future months (i.e. cells B2 & C2) I use the formula’s:

(B2) =DATE(YEAR(A2),MONTH(A2)+1,DAY(A2))
(C2) =DATE(YEAR(B2),MONTH(B2)+1,DAY(B2))

i.e. adding +1 to the month of the cell referenced

This works fine mostly, but when you get to the end of Jan, the + 1 month takes you beyond Feb and so displays as March (skewing the figures)
Any ideas how to adapt this to calculate months ahead as desired?

N
 
On Jan 30th, the B2 formula is =DATE(2017,2,30). There aren't 30 days in Feb so it looks like it's "wrapping around" into March. Changing the formatting temporarily to yyyy-mmm-dd would let you see what it's doing.
Depending on how you use these cells, one option might be to use the first day of the month in the formula, i.e. =DATE(YEAR(A2), MONTH(A2)+1, 1)? This would need testing to see how it behaves in December - hopefully month 13 is treated as month 1 in the following year.
 
You must log in or register to reply here.
Back
Top Bottom