Excel Tip – Dynamically Return the First or Last Day of a Month

ExcelIn Excel, you can use the EOMONTH() function to return the first or last day of a month.

Syntax

EOMONTH(start_date, months)

The EOMONTH function syntax has the following arguments:

Start_date

  • Required. A date that represents the starting date. Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2015,7,23) for the 23rd day of July, 2015. Problems can occur if dates are entered as text.

Months

  • Required. The number of months before or after start_date. A positive value for months yields a future date; a negative value yields a past date.

NOTE If months is not an integer, it is truncated.

Examples

To return the LAST day of the current month:

=EOMONTH(TODAY(),0)

The 0 in the second parameter denotes the current month.

To return the FIRST day of the current month:

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

This means, return the last day of the previous month PLUS one day (i.e. the first day of the current month).

By the same token, to return the FIRST day of the next month:

=EOMONTH(TODAY(),0)+1

The result will be a serial number.  To convert this to a displayable string:

=TEXT(EOMONTH(TODAY(),1), "MM/DD/YYYY")
Advertisements
Published in: on July 17, 2015 at 9:57 pm  Leave a Comment  

The URI to TrackBack this entry is: https://blackbeltreview.wordpress.com/2015/07/17/excel-tip-dynamically-return-the-first-or-last-day-of-a-month/trackback/

RSS feed for comments on this post.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: