Changing date in microsoft excel program, for each month for 30 years.?

Question

hello,

My question is how do I write a excel formula that one cell reads today's date and the cell below it reads next month but today's date. Example: 12/22/2010, 1/22/2011, 2/22/2011, and so on. Also the next day should read 12/23/2010, 1/23/2011, 2/23/2011, and so on. I have tried many methods but due to difference in number of days in a month and leap year and all that, I cannot get this to work. I would like to have this date go on for the next 30 years, for each month.

? · Accepted Answer

How about on the months that don't have 31 days to just show the last day of that month instead? For example, on 1/30/2011 the next month will show 2/28/2011. To do this, put this formula somewhere on the sheet

=TODAY()

Let's say you put the TODAY formula in A2. Paste this formula in A3

=MIN(DATE(YEAR(A$2), MONTH(A$2)+ROW(N1), DAY(A$2)),EOMONTH(A$2, ROW(N1)))

and copy down about 360 rows. (you can test it by putting in 12/31/2010 and 12/30/2010 in A2.)

As an alternative, if you'd rather have blank cells instead of the last day of the short months, paste this formula in A3

=IF(EOMONTH(A$2, ROW(N1))>=DATE(YEAR(A$2), MONTH(A$2)+ROW(N1), DAY(A$2)),DATE(YEAR(A$2), MONTH(A$2)+ROW(N1), DAY(A$2)),"")

Scrawny · Answer

The question is impossible using worksheet functions because retaining the date from one month to the next is not always possible. If a month has 31 days followed by a month of 30 days or less, you have an impossible situation. February doesn't have 30 or 31 days....ever and it has 29 day almost every 4 years as there are exceptions to this also.

The only solution is VBA code that takes into every conceivable "exception" that is possible. You will have to set out rules to follow when and impossible situation arises.

Anonym · Answer

You can use the following formula to have it automatically recognize the current month.    ="1/" & MONTH(TODAY()) & "/2013" However, to build logic for removing weekends would require you to write visual basic code.

Changing date in microsoft excel program, for each month for 30 years.?

3 Antworten