Spreadsheets: No More Mystery Dates

Learn the Excel formulas that allow you to calculate the first and last day of the month so you can group all transactions by their respective months.


Editor’s Note:

To read more about spreadsheets, share a tip with other readers, or suggest a topic for Bill Jelen to cover in an upcoming column, click here to visit CFO.com’s Spreadsheet Tips page.

Question: We have a database of service contracts. There is a transaction-date column in the data. How can I calculate the first or last of the month in order to group all transactions by month?

First: Do You Have Real Excel Dates?

Excel offers an amazing array of date functions, but they will only work if your data contains true Excel dates. One quick way to check: look on your keyboard for the grave accent (‘) key. It is usually combined with the tilde (~) character below the Esc key. Press Ctrl and the grave accent to put your worksheet in Show Formulas mode. In this mode, your date column should switch to showing numbers in the 39,000-41,000 range. If your date column still shows dates, then you have text dates.

Press Ctrl and the grave accent again to toggle out of Show Formulas mode.

If you need to convert text dates to real dates, select the column containing the text dates. Press Alt+D followed by e and f. This will use Text to Columns to convert the text dates to real dates.

Calculating the First of the Month

Say that your date is in cell F2. Enter this formula to calculate the first of the month:

                =F2-DAY(F2)+1

If F2 contains August 17, 2010, the DAY function will return 17 to represent the day of the month. When you subtract 17 from August 17, you end up at July 31. Add 1 to the result to get back to the first of August.

Calculating the Last of the Month

If you are in Excel 2007 or Excel 2010, or if you are using an older version of Excel and have activated the Analysis ToolPak, then you could use the EOMONTH function to calculate the end of the month. =EOMONTH(F2,0) will return the date at the end of the month, although you will have to format the result as a date.

If you cannot ensure that your worksheet won’t be opened in Excel 2003, then you can use a combination of the DATE, YEAR, and MONTH functions. While you might initially think that you would need special handling for February, April, June, September, and November, you can use a clever trick to find the last of this month; use the DATE function to find the first of the next month and then subtract 1.

=DATE(2010,9,1) will return the Excel date for September 1, 2010. Subtracting 1 from this date will return August 31, 2010. Rather than hard-coding 2010 and 9 in the formula, you can use:

=DATE(YEAR(F2),MONTH(F2)+1,1)-1

You might wonder if this formula would work for dates in December. After all, the formula would try to find the first day of the 13th month of 2010. Excel has no problem figuring out that the 13th month of 2010 is January 2011. The figure below shows the results of the formula for various dates.

MrExcel 9-15

Bill Jelen is the author of 32 books, including Pivot Table Data Crunching, and is the host of MrExcel.com. Suggest a topic for his next column at CFO.com’s Spreadsheet Community Center (right) and if your suggestion is chosen, you’ll receive a copy of one of Jelen’s new books.

Discuss

Your email address will not be published. Required fields are marked *