5 Ways To Get Last Month In Excel

Managing dates in Excel can be a bit tricky, but there are several ways to get the last month's date in Excel. Whether you're working with a specific date or a dynamic range, we'll explore five methods to help you achieve this.

Why Get Last Month's Date in Excel?

Getting the last month's date can be useful in various scenarios, such as:

Creating a dashboard that displays data from the previous month
Automating reports that require last month's data
Updating charts and graphs to reflect the previous month's trends
Performing calculations that rely on the last month's date

Method 1: Using the EOMONTH Function

The EOMONTH (End of Month) function is a straightforward way to get the last month's date. This function returns the last day of the month for a given date.

=EOMONTH(TODAY(), -1)

This formula uses the TODAY() function to get the current date and then subtracts one month using the EOMONTH function.

Method 2: Using the DATE Function

Another way to get the last month's date is by using the DATE function. This function returns a date value from a given year, month, and day.

=DATE(YEAR(TODAY()), MONTH(TODAY())-1, DAY(TODAY()))

This formula uses the YEAR, MONTH, and DAY functions to extract the current year, month, and day, and then subtracts one month using the DATE function.

Method 3: Using the EDATE Function

The EDATE (Equivalent Date) function returns a date that is a specified number of months before or after a given date.

=EDATE(TODAY(), -1)

This formula uses the TODAY() function to get the current date and then subtracts one month using the EDATE function.

Method 4: Using a Formula with DAY and MONTH Functions

You can also use a combination of the DAY and MONTH functions to get the last month's date.

=DATE(YEAR(TODAY()), MONTH(TODAY())-1, 1) - DAY(DATE(YEAR(TODAY()), MONTH(TODAY())-1, 1)) + 1

This formula uses the DAY and MONTH functions to extract the current day and month, and then subtracts one month using the DATE function.

Method 5: Using Power Query

If you're using Excel 2010 or later, you can use Power Query to get the last month's date.

Go to the "Data" tab and click on "New Query" > "From Other Sources" > "Blank Query".
In the Query Editor, click on "Add Column" > "Custom Column".
Enter the following formula: =Date.Year(Today()) & "-" & Date.Month(Today())-1 & "-01"
Click "OK" to create the new column.
Load the query into a new worksheet.

This method uses Power Query to create a new column that displays the last month's date.

Gallery of Excel Date Functions

Frequently Asked Questions

What is the easiest way to get the last month's date in Excel?

The easiest way to get the last month's date in Excel is by using the EOMONTH function, which returns the last day of the month for a given date.

Can I use the DATE function to get the last month's date?

Yes, you can use the DATE function to get the last month's date by subtracting one month from the current date using the MONTH function.

How do I use Power Query to get the last month's date in Excel?

You can use Power Query to create a new column that displays the last month's date by using the Date.Year and Date.Month functions in the Query Editor.

We hope this article has helped you learn how to get the last month's date in Excel using five different methods. Whether you prefer using formulas or Power Query, we've got you covered.