Excel day of month

Microsoft Excel 2007
September 28, 2009 at 08:57:07
Specs: Windows 7
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.

See More: Excel day of month

Report •

September 28, 2009 at 09:36:46

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:

January		31
February	28
March		31
April		30
May		31
June		30
July		31
August		31
September	28

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.

Report •

September 29, 2009 at 00:49:02
Thank you. That is exactly what I needed, it works perfectly, you are a life saver.

Report •

September 29, 2009 at 01:53:25
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

Report •

Related Solutions

September 29, 2009 at 05:19:53

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!

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.


Report •

September 29, 2009 at 07:34:52

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.


Report •

September 29, 2009 at 08:23:07
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.

Report •

September 29, 2009 at 08:37:01
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:

for today (29 Sep) this returned 272


Report •

September 29, 2009 at 17:07:16
Hi Humar
I have applied your update to my sheet

The 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

Report •

September 30, 2009 at 05:39:52

Replace the formula in C3 (January) with this:
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.


Report •

September 30, 2009 at 05:56:50

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.


Report •

September 30, 2009 at 06:38:20

Your welcome


Report •

Ask Question