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 $ signsFormat 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 monthB14 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 DecemberRegards

PS if you try this in Excel 2003 and it doesn't work, you likely do not have the EOMONTH function installedEOMONTH 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.

Hi markedney, Glad to have been of help.

As to the +1 etc. it may depend on where in the formula you put the adjustment.

I used:

=DATEDIF(B3,NOW(),"d")+1for today (29 Sep) this returned 272

Regards

Hi Humar

I have applied your update to my sheet

=DATEDIF(B3,NOW(),"d")+1The reason for my spreadsheet id to calculate payments made by residents in a care home based on daily rate. I can now calculate the total fees due to date using the datedif formula and I have a live monthly list including current month in days.

I still have a problem however. I need to calculate monthly arrears or overpayments as list below.

I have tried to apply the datedif formula to the days in column c but this does not seem to work at all.

If the days were an actual number as opposed to a date I would simply multipy the day rate by the number of days in the month to calculate amount due then subtract the sum actually paid but This will obviously not work. Any help will be greatly appreciated.

2009 days Paid Arears/OP

January 31 £ £

February 28 £ £

March 31 £ £

April 30 £ £

May 31 etc

June 30

July 31

August 31

September 28

October

November

December

Hi, Replace the formula in C3 (January) with this:

=IF(ISERROR(DAY(IF(NOW()>=B3,IF(MONTH(NOW())=MONTH(B3),DAY(NOW()),EOMONTH(B3,0)),""))),0,DAY(IF(NOW()>=B3,IF(MONTH(NOW())=MONTH(B3),DAY(NOW()),EOMONTH(B3,0)),"")))

Then drag down to December.The formula converts the Excel date serial number into a day number.

(The original formula is wrapped in the DAY() function).However, the formula gives an error when there is no date in the cell

(as in all future months), so the formula has to check for the error (IF(ISERROR(

and return zero instead.

If no error it just uses the original formula wrapped in DAY().Now you can use the values as days for further calculation.

Regards

Humar

Thank you so much. I can now acheive exactly what I set out to. I really appreciate your help and the time you have spent on replying to my posts.Regards

Mark

Hi, Your welcome

Regards

Humar

Ask Your Question

Weekly Poll

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History