Hello,

I'm using Excel 2007 and would like the dates to appear as: Jun/01, Jun/02,
Jun/03, Jun/04, Jun/05, Jun/06, Jun/07, Jun/08, Jun/09. I entered the first
date in cell B2 as such 6/01/01 and custom format as mmm/yy.

I go to cell C2 and enter a formula =B2+365 so it will display as Jun/02.
Everything is fine until I get to cell E2 which should display Jun/04, but
instead it displays as May/04. The formula contains =D2+365.

Why did the month change? I need to plot dates in a bar chart with the
following dates: 6/01/01 through 6/01/09 in Jun/01 through Jun/09 format.

The issue is with leap years.

try using c2 = date (year(b2)+1, month(b2),1)

and this should work.

--
Hope this helps
Martin Fishlock, www.nyfconsultants.com
Please do not forget to rate this reply.


"Bagia" wrote:

Hello,

I'm using Excel 2007 and would like the dates to appear as: Jun/01, Jun/02,
Jun/03, Jun/04, Jun/05, Jun/06, Jun/07, Jun/08, Jun/09. I entered the first
date in cell B2 as such 6/01/01 and custom format as mmm/yy.

I go to cell C2 and enter a formula =B2+365 so it will display as Jun/02.
Everything is fine until I get to cell E2 which should display Jun/04, but
instead it displays as May/04. The formula contains =D2+365.

Why did the month change? I need to plot dates in a bar chart with the
following dates: 6/01/01 through 6/01/09 in Jun/01 through Jun/09 format.

Thanks Martin.

Maybe you know an answer to this madness that I'm experiencing. I created 2
files with the same date information except the only difference is the
format. In the first file, the first date is shown as a number, whereas in
the second file the date is shown as a date mmm/yy.

When I chart the data, the first file the chart displays correctly, Jun-01
through Jun-09. In the second file, the chart displays as Jan-01 through
Jan-09. I don't understand why this is happening.



"Martin Fishlock" wrote:

The issue is with leap years.

try using c2 = date (year(b2)+1, month(b2),1)

and this should work.

--
Hope this helps
Martin Fishlock, www.nyfconsultants.com
Please do not forget to rate this reply.


"Bagia" wrote:

Hello,

I'm using Excel 2007 and would like the dates to appear as: Jun/01, Jun/02,
Jun/03, Jun/04, Jun/05, Jun/06, Jun/07, Jun/08, Jun/09. I entered the first
date in cell B2 as such 6/01/01 and custom format as mmm/yy.

I go to cell C2 and enter a formula =B2+365 so it will display as Jun/02.
Everything is fine until I get to cell E2 which should display Jun/04, but
instead it displays as May/04. The formula contains =D2+365.

Why did the month change? I need to plot dates in a bar chart with the
following dates: 6/01/01 through 6/01/09 in Jun/01 through Jun/09 format.