Explanation

The MINIFS function returns the smallest numeric value that meets supplied criteria, and the MAXIFS function returns the largest numeric value that meets supplied criteria.

Like COUNTIFS and SUMIFS, these functions use range/criteria “pairs” to apply conditions. For both formulas, we need just one condition: the project name must equal the name in column G:

data[Project],G5 // condition

To get the earliest start date, we use:

=MINIFS(data[Start],data[Project],G5) // earliest date

Here, MINIFS returns the minimum value in the Start column where the project is equal to “Omega” (from cell G5). Since Excel dates are just numbers , the minimum date is the same as the earliest date.

To get the latest end date, we use:

=MAXIFS(data[End],data[Project],G5) // latest date

Here, MAXIFS returns the maximum value in the End column where the project is equal to “Omega”. As above, the maximum value is the same as the latest date.

Array formula alternative

If you don’t have MINIFS and MAXIFS, you can use simple array formulas, based on the MIN and MAX functions, to get the same result. For the earliest start date:

{=MIN(IF(data[Project]=G5,data[Start]))}

For the latest end date:

{=MAX(IF(data[Project]=G5,data[End]))}

Note: both formulas are array formulas and must be entered with control + shift + enter, in Excel 2019 or earlier. With Excel 365, you can enter the formulas normally, since array formulas are native .

In both cases, the IF function is used to “filter” date values like this:

IF(data[Project]=G5,data[End]) // filter dates by project

When G5 is “Omega”, IF returns the end date. Otherwise, IF returns FALSE. Since we are testing all project names in the table at the same time, the result is an array of values like this:

{43936;43983;43990;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

The large serial numbers are Excel dates associated with project Omega. The other values are FALSE, since the project is not Omega. Because MIN and MAX are programmed to ignore the logical values TRUE and FALSE, they only operate on the remaining values. MIN returns the smallest (earliest) date, and MAX returns the largest (latest) date.

Explanation

In this example, the goal is to get the first day of the month based on any valid date. This problem can be solved by combining the EOMONTH function with simple addition.

The EOMONTH Function

The EOMONTH function returns the last day of the month, a given number of months in the past or future. For example, with a start date of January 15, 2025, EOMONTH will return the following results with months set to -1, 0, and 1:

=EOMONTH("15-Jan-2025",-1) // returns 31-Dec-2024
=EOMONTH("15-Jan-2025",0)  // returns 31-Jan-2025
=EOMONTH("15-Jan-2025",1)  // returns 28-Feb-2025

Note that the start date remains the same, but the month varies. Negative months cause EOMONTH to move back in time, and positive months move forward, but the result is always the end of the month.

How the formula works

The formula =EOMONTH(B5,-1)+1 works in two steps. First, it moves back to the last day of the previous month, relative to the given date. Next, it adds one day to end up on the first day of the current month. Working from the inside out with a start date of January 12, 2025:

=EOMONTH("12-Jan-2025",-1)+1  // move back 1 month
="31-Dec-2024"+1  // add 1 day
="1-Jan-2025"  // final result

The result is always the first day of the month for any given date. As the formula is copied down, the process is repeated for each date in column B.

First day of current month

To get the first day of the current month, you can use the same approach but replace the date reference with the TODAY function :

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

This formula will automatically update each day to show the first day of whatever month it currently is. For example, if today is June 21, 2025, the formula will return June 1, 2025. If you open the same worksheet in July, the result will be July 1, 2025. The formula works exactly the same way as the main example:

=EOMONTH(TODAY(),-1)+1
=EOMONTH("21-Jun-2025",-1)+1  // get current date
="31-May-2025"+1  // move back 1 month
="1-Jun-2025"  // add 1 day

This approach is useful in reports and dashboards that need to display current-month information. Since TODAY recalculates whenever the worksheet recalculates, this formula will always return the first day of the current month.

Variations

You can easily change how this formula works by changing the number of months used inside the EOMONTH function. The pattern EOMONTH(date, n-1)+1 will give you the first day of the month that is n months away from your reference date:

=EOMONTH(B5,0)+1 // first day of next month
=EOMONTH(B5,-1)+1 // first day of current month
=EOMONTH(B5,-2)+1 // first day of previous month
=EOMONTH(B5,-7)+1 // first day of month 6 months ago
=EOMONTH(B5,5)+1 // first day of month 6 months forward

To move forward or backward n months without changing the date, see the EDATE function .

Alternative formula

You can also get the first day of a month using the DAY function in a formula like this:

=B5-DAY(B5)+1

This formula was the standard way to get the first day of the month before the EOMONTH function was introduced to Excel. This formula works in three steps:

  1. Get the day number from the date with DAY(B5) .
  2. Subtract the day from the date (rewind to day 0 = last day of the previous month).
  3. Add 1 day to end up on the first day of the current month.

For example, with a start date of January 12, 2025:

=B5-DAY(B5)+1
="12-Jan-2025"-12+1
="31-Dec-2024"+1
="1-Jan-2025"

Both formulas work reliably, but the EOMONTH formula is slightly easier to understand and configure.