Calculating the Last Day of the Month in Excel

In Microsoft Excel, various functions exist to calculate the date and time from the current date to the past and future. Among multiple tasks in this tutorial, let's see how to calculate the last day of the month in Excel. To calculate this EOMONTH function is used, and EOMONTH is called End of Month, which calculates the expiry date, due date and various other dates.

To find the future date use a positive value, and to see the past dates add a negative value.

Syntax Used

=EOMONTH (start_date, months)

Parameters

start_date – It represents the start date in an Excel serial number format

months – It represents the number of months in the future or past.

How to calculate the first day of the month?

STEP 1: Select a cell in the worksheet and enter the required date in the format mm/dd/yyyy.

STEP 2: Choose another cell (B1) and enter the formula as =DATE (YEAR (cell name), MONTH (cell name), 1).

STEP 3: The first day of the month is displayed in cell B1 using a formula.

Here DATE function comprises three arguments such as year, month and day. The YEAR function retrieves the year, and the MONTH function retrieves the month of the date.

How to calculate the last day of the current month?

STEP 1: Select a cell and type the formula as =EOMONTH (TODAY (), 0)

STEP 2: The result is displayed in cell A1.

STEP 3: This formula calculates the last day of the current month. Different procedure is used to calculate the last day of the previous or future month.

Calculating the last day of the Month

STEP 1: To calculate the last day of the month, select a cell (A1) and enter the required date.

STEP 2: Choose another cell (B1) and enter the formula as =EOMONTH (cell name, 0).

STEP 3: The last day of the month for the given data is displayed as shown below.

Calculating the last day of the month, one month after the present month

Here, this method calculates the last day of the month, which is one month after the current month.

STEP 1: Select a cell in a worksheet and enter the required date.

STEP 2: Choose another cell where the results must be displayed and type the formula as =EOMONTH (cell name, 1).

STEP 3: The result will be displayed one month after the given date.

Calculating the last day of the month, one month before the present month

Here, this method calculates the last day of the month, which is one month before the current month.

STEP 1: Select a cell in a worksheet and enter the required date.

STEP 2: Choose another cell where the results must be displayed and type the formula as =EOMONTH (cell name, -1).

STEP 3: The result will be displayed one month before the given date.

One can find the previous date from the current date by the user's choice number of the last month. Using the '-' sign reduces the number of months.

Calculating the first day of the month

STEP 1: Select a cell (A1) and enter the required date.

STEP 2: Choose another cell (B1) and enter the formula as =DATE(YEAR(cell name),MONTH(cell name),1).

STEP 3: The first day of the month will be displayed as shown below.

Summary

The above tutorial discusses the various formulas to find the present, past and future dates.