Calculate End of Month using EOMONTH function

You may not have seen many people using this formula. However, it is as useful as SUM formula at times. During this post, I will guide you about syntax of formula and its uses. Like the title of post says, it is used to find end of month. I will share some very important scenario's and method to find beginning month using same formula.

The function return's end of month and it requires the input as Date. Also, since it return's the date as number you will have to format cell to show dates.

Copy-Paste the table in MS Excel starting from A1.

Date	Month	Formula	Returns
6-Mar-09	-1	=EOMONTH(G11,H11)	28-Feb-09
6-Mar-09	1	=EOMONTH(G12,H12)	30-Apr-09
6-Mar-09	2	=EOMONTH(G13,H13)	31-May-09

Formula: =EOMONTH(StartDate,Months)

In the above example, -1 with 6-Mar-09 will return you the end of Feb. Similarly, 0 will return current month and 1 will return next month.

Example1: You want to find the number of days remaining in current month.

Formula: =EOMONTH(TODAY(),0)-TODAY()

TODAY() will return the current date and zero will tell EOMONTH to consider current month. Difference between EOMONTH(TODAY(),0) and TODAY() will return number of days left in month.

Example2: You want to find the first of current month.

Formula: =EOMONTH(TODAY(),-1)+1

TODAY() will return the current date and -1 will tell EOMONTH to consider previous month. Adding 1 to end of previous month will return 1st of current month.

Kindly let me know your views about post. Subscribe via email to blog to receive latest updates in your inbox.

We assure you knowledge, not SPAM!