How can I retrieve the first day of the previous month in Excel?

By /

To retrieve the first day of the previous month in Excel, you can use the EOMONTH function to get the last day of the previous month, and then subtract the number of days in that month to get the first day. Another option is to use the DATE function to specify the previous month and the first day of that month. You can also use the TODAY function to get the current date, and then use the DAY, MONTH, and YEAR functions to extract the relevant information and create a new date with the first day of the previous month.

You can use the following formula to get the first day of the previous month in Excel:

=EOMONTH(A2,-2)+1

This particular formula returns the first day of the previous month for the date in cell A2.

For example, if cell A2 contains the date 2/12/2024 then this formula would return 1/1/2024.

The following example shows how to use this formula in practice.

Example: How to Get First Day of Previous Month in Excel

Suppose we have the following dataset in Excel that contains information about sales made on various dates at some company:

Suppose that we would like to find the first day of the previous month for each date in the Date column.

To do so, we can type the following formula into cell C2:

=EOMONTH(A2,-2)+1

We can then click and drag this formula down to each remaining cell in column C:

Excel get first day of previous month

Column C now shows the first day of the previous month for each corresponding date in column A.

For example:

  • The first day of the previous month for 1/5/2024 is 12/1/2023.
  • The first day of the previous month for 1/18/2024 is 12/1/2023.
  • The first day of the previous month for 2/12/2024 is 1/1/2024.

And so on.

How This Formula Works

Recall the formula that we used to find the first day of the previous month for cell A2:

=EOMONTH(A2,-2)+1

Here is how this formula works:

The EOMONTH function in Excel returns the last day of the month that is the indicated number of months before or after some date.

By using EOMONTH(A2, -2) we are able to find the last day of the month for the month that is two dates before the month in cell A2.

For example, if the date in cell A2 is 1/5/2024 then this formula returns 11/30/2023.

Then, we simply add 1 to get the first day of the following month, which is 12/1/2023.

Note: You can find the complete documentation for the EOMONTH function in Excel .

Additional Resources

The following tutorials explain how to perform other common operations in Excel:

x

Related terms:

  1. What is the MySQL query to retrieve the last day of the previous month?
  2. How can I get the first day of the previous month using MySQL?
  3. How to fix Error in R: names do not match previous names
  4. Excel: Split Date into Day, Month and Year
  5. Excel: Find the First Monday of Each Month
  6. How to Check if Month and Year Match Between Dates in Excel
  7. How to check if month is January in Excel?
  8. How to Sum by Month in Excel (Step-by-Step Example)
  9. How to Filter Dates by Month in Excel (With Example)
  10. How can I get the same date next month in Excel?