Dates in Excel

[coquinn]

I am trying to get even annual intervals in Excel however am unable to do it. For instance I want the first date to be 6th Nov 2005, the next to be 6th Nov 2006, the next to be 6th Nov 2007,....and so on. Does anyone know a command that will allow me to do this (as opposed to just adding 365 days, which breaks down in a leap year)...Thanks

 

[gaynard_nelson]

I am trying to get even annual intervals in Excel however am unable to do it. For instance I want the first date to be 6th Nov 2005, the next to be 6th Nov 2006, the next to be 6th Nov 2007,....and so on. Does anyone know a command that will allow me to do this (as opposed to just adding 365 days, which breaks down in a leap year)...Thanks

If you enter the first two dates 11/06/05 & 11/06/06, select these two dates and drag the fill handle as far as you want the dates to display Excel will duplicate the year interval in succeeding entries.

 

[coquinn]

Thanks..but my day changes daily (i.e. I am using the today command)...so is there any way to automate the process?

 

[MTBer]

assuming date is in cell A1, cell A2 the formula is

=date(year(a1)+1,month(a1),day(a1))

alternatively,

tools, addins, analysis toolpak, and use the EDATE function

=edate(a1,12)

Paul

 

[BrianDP1977]

I'm no expert but another format of the same equation posted by MTBer would be =A1 + DATE(2,0,0) where A1 is a cell referencing todays date (i.e. =TODAY()).

 

[vane0326]

In A4 and B4 use those formulas and format the cells this way:

d "th" mmm yyyy

Right click on the cell B4 go to custom and type it in. Then copy the formula across the columns.

Book2.xls
	A	B	C	D	E	F	G
1
2
3
4	6 th Nov 2005	6 th Nov 2006	6 th Nov 2007	6 th Nov 2008	6 th Nov 2009	6 th Nov 2010	6 th Nov 2011
5
6
Sheet1