Coming up with the last day of the month in Excel is simple but not readily apparent. By using the DATE function in combination with the YEAR, MONTH, and DAY functions, all you have to do is take the next month (MONTH(reference)+1) and then set the day to zero (DAY(0)). The final formula would look like this:

 =DATE(YEAR(reference),MONTH(reference)+1,DAY(0))

Here’s an example. You are responsible for managing your marketing budget. You are given a budget forecast for the next year that lists the budget in monthly amounts. Let’s also suppose that for your purposes you need to know your budget on a daily basis and your budget varies from month to month (you can’t just divide the total by 365). Using the method above to find the last day of the month you can build out a worksheet like this:

The first two columns are the dates and the budget which was given to you. The third column gives us the days in the month that we can then use as the denominator with the budget as the numerator to get the Daily Budget. The formula for this column would look like this:

=DATE(YEAR(A2),MONTH(A2)+1,DAY(0))-A2+1

What this does is take the last day of the month, subtract the first day of the month, and then adds one (because we subtracted dates up to and including the first day). Dragging the formula down will then provide the appropriate information for all months.

This entry was posted on Thursday, May 31st, 2007 and is filed under Whatever, Excel. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.