MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Number of days in a month

Posted by SJC on January 10, 2001 12:26 PM

Is there an Excel function that can give the number of days during a particular month?

In my spreadsheet, I have cell A1 with a value of December 2000. In cell B1, I have a formula that calculates a score involving several variables, including cell A1. Cell A1 will be dynamically changed.

Also, how would you calculate the number of days passed for the month-to-date. For example, for this month, 9 complete days have passed.

Posted by Mark W. on January 10, 2001 12:51 PM


Posted by GREG on January 10, 2001 12:54 PM

RESPONSE: The EOMONTH function shows you the last day of the month, which would give you the amount of days in the month. I am not sure if there is another function that does that, but there may be. To get the beginning of the month you can use the EOMONTH function for the preceeding month and add 1 to it and it gives you the first day of the month. From there you just subtract the value in the begining of the month number from the NOw() and you have the amount of days gone by for the month.

Posted by Mark W. on January 10, 2001 1:15 PM

After Greg's assistance I can now see that I
misunderstood your request. If cell A1 will
always contain a date from the previous month
and you want to calculate the number of days
that have based in the current month. You can
use (but there are oodles of ways to do this):


Posted by Scott on January 10, 2001 1:19 PM

Days in Month: =DAY(EOMONTH(A1,0)) EOMONTH is from "Analysis TookPak" Add-In

Days Expired: =DAY(TODAY())-1