How many days in a month?

What's an EOMONTH?

Recently I saw a post referenced in an email that mentioned the EOMONTH() function.  Given a date, plus the number of months before or after that date, it returns the last day of that month.

=EOMONTH(StartDate,MonthOffset)

For more, here is the "official" Microsoft entry, as of today.

I had never used it, but it seemed interesting.

Cute trick, but so what?

This made me think of the number of times I needed a rolling table for reports, showing results and/or forecasts, so I built a simple table of twelve rows.

The only data to enter is the first Relative Month, cell A2 for the example.  In this case, "-5" is five months  in the past from whatever date is used to seed EOMONTH().  The remainder of the Relative Month column is simply adding one to the cell above it: Cell A3 is "=A2+1".

For this example I used NOW() to provide the starting date.  

=EOMONTH(NOW(),A2)

Where's the date?

I found the normal date formats to be somewhat less interesting than the component parts, so I formatted column B, where I put the EOMONTH() function using the "mmmm" formatting.

For column C I used "yyyy"; D & E "dddd"and F "dd".  That gives me the name of the month, the year, the names of the days of the week, and the last day of the month as the number of days in the month.

As another alternative, you could use the FORMAT() function or

• B2 =CHOOSE(MONTH(EOMONTH(NOW(),A2)),"January", "February","March", "April", "May", "June","July","August","September", "October","November","December")
• C2 = YEAR(EOMONTH(NOW(),A2))
• D2 = CHOOSE(WEEKDAY(EOMONTH(NOW(),A2)),"Sunday", "Monday","Tuesday", "Wednesday", "Thursday", "Friday","Saturday")

No need to overuse EOMONTH()

I then entered "=$A2" in cell C2, copying down then dragging down the columns.  

"Why?", you may ask, "Don't you just repeat the formula?"

Several reasons:  

• Speed of recomputing the worksheet
• Fewer points of failure if I have a mistake
• I'm lazy

First of the Month

I can come up with a couple of ways to use worksheet functions to get the first day of the month, column D, iI including variations of the above.  Another possibility might be , keeping with the "re-use column B" motif, "=DATE(YEAR(B2),MONTH(B2),1)".

I settled on finding the last day of the previous month ("EOMONTH(NOW(),A2-1)"  then  adding "1" since I knew that in the Microsoft world "1" is a day/ 

=EOMONTH(NOW(),A2-1)+1

This was already formatted with "dddd".