Excel beginner date eomonth first-day last-day

How to Find the First and Last Day of a Month in Excel

First and Last Day of Month is a Excel function that calculates the first and last day of the month for a given date in cell a1.. Formula Genius generates and validates this formula automatically from a plain-English prompt.

Easily determine the first and last days of any month from a given date using simple Excel formulas.

Generate This Formula Browse Library

The Formula

Prompt

"Find both the first day and last day of any given month from a date value"

Excel 
=EOMONTH(A1,0) - DAY(EOMONTH(A1,0)) + 1 & " to " & EOMONTH(A1,0)

This formula calculates the first and last day of the month for a given date in cell A1.

Step-by-Step Breakdown

  1. Use EOMONTH(A1, 0) to find the last day of the month.
  2. Subtract DAY(EOMONTH(A1, 0)) to get the first day of the month.
  3. Add 1 to the result to adjust to the first day.
  4. Concatenate the first and last days with ' to ' in between.

Edge Cases & Warnings

  • Input date is the last day of the month.
  • Input date is in a leap year.
  • Input date is the first day of the month.
  • Input date is formatted as text instead of a date.

Examples

Prompt

"2023-03-15"

Excel 
2023-03-01 to 2023-03-31
Prompt

"2024-02-28"

Excel 
2024-02-01 to 2024-02-29

Frequently Asked Questions

What if the input date is invalid?

The formula will return an error if the input date is not valid.

Can I use this formula for any date format?

The formula works best with recognized date formats in Excel.

How does this formula handle leap years?

The formula correctly identifies February 29 for leap years.

Can't find what you need?

Describe any formula in plain English and Formula Genius will generate, explain, and validate it — instantly.

Generate Custom Formula See Examples