please help. I am trying to create a list of months January thru December with coresponding current day of the month in an adjacent cell i.e 26 for the 26th day of the current month. when the month is over I would like the day cell to automatically revert to the last day in month and current day move down 1 cell to the following to the following month.

Hi, Here is a solution: Your year and month will go in column B and the days in column C In cell B2 enter "01-01-2009" Format B2 with a custom format "yyyy" In cell B3 enter =DATE(YEAR($B$2),"1","1") note the $ signs Format B3 with a custom format "mmmm" Drag the formula in B3 down to B14 All the cells under 2009 will show January Now edit each formula starting with B4, changing the month B14 will contain =DATE(YEAR($B$2),"12","1") In C3 enter the following formula =IF(NOW()>=B3,IF(MONTH(NOW())=MONTH(B3), DAY(NOW()),eomonth(B3,0)),"") Format cell C3 with a custom format "dd" Drag the formula in C3 down to C14 This is what my worksheet looks like: 2009 January 31 February 28 March 31 April 30 May 31 June 30 July 31 August 31 September 28 October November December Regards PS if you try this in Excel 2003 and it doesn't work, you likely do not have the EOMONTH function installed EOMONTH is end of month and gives the last day in a month (takes account of leap years) To activate EOMONTH go to the tools menu - Add-Ins Look for Analysis toolpack or Analysis toolpack - VBA and check one of them Click OK Back on your worksheet hit f9. AFAIK EOMONTH is standard in Excel 2007.

Thank you. That is exactly what I needed, it works perfectly, you are a life saver.

I failed to include the fact that I need to auto calculate the current number of days at any given date ie if the current date is 29th Sept 2009 the answer will be 272. If I use AutoSum to total the day column this gives me a figure of 01 not 272

Hi, In column C, the days that you are seeing are a formatted representation of an Excel date value. The 01 total is also a formatted representation of the total - Excel decided that as you were summing cells formated "dd", it would do the same for the Sum. Go into your Sum cell and change the format from "dd" to a number format and you will see a very large number! Solutions: The simplest solution is to use a poorly documented Excel function DATEDIF I am not at a PC with 2007 installed, so I can't test it in 2007. In 2003 it works and you just use it to get the number of days since 01 January, (cell B2 has 01-Jan-2009, although formatted to show only the year). For today, use Now(). This site gives you the syntax. If that doesn't work just do the simple subtraction of "01-January-2009" from Now() , and take the integer part of the result +1. Excel holds dates/times as a number. The Integer part is the date and the decimal part is time. 0.5 is 12 noon, i.e., halfway through the day. If today's date number is 40085, tomorrow will be 40086. The big thing to remember about dates/times is that what you see depends on the date/time formatting, but what Excel stores is a number that started at a fraction of a second after midnight, on 01 January 1900 and increases by one each day. (There is an error in Excel in February 1900 - it wasn't a leap year but Excel has a value for 29 Feb 1900). Try entering 27-Feb-1900 in two adjacent cells. Format one as a date "dd-mmm-yyyy" and the other as a number with no decimals. Drag both values down a few rows and you will see 29-Feb-1900, a date that did not exist. Surprisingly if you work in Visual Basic for Applications (VBA) the result is different. AFAIK VBA treats serial #1 as 31 December 1899, and thus without 29 February 1900, the number sequence is consecutive for dates that existed. Regards

Hi, I am now at a PC with Excel 2007, and datedif() works, but remains undocumented. If you use datedif() you may need to add 1 to the result to get the value you are looking for. Regards

Thank you Humar. DATEDIF worked and I have exactly what I needed. I did have to subtract 1 to get the correct number of days (adding 1 actually reduced the correct figure by 2 days) I don't know why. Once again thank you, I could not have done this without you.