Excel Formula: Check If Date Is Last N Month from Today

Supposing you need to check if staff’s entry date is last 6 months from today, you can use a formula combined by AND, EOMONTH and TODAY functions in Excel to quickly handle the job.

If you want to follow along with this tutorial, please download the example spreadsheet.

Generic formula

Syntaxt and arguments

Return value

If the date is last n month of today, it returns TRUE;
If the date is not last n month of today, it returns FALSE.

How this formula works

To check if the dates in range C3:C9 are last 6 months from today, you can use the formula like this:

Or directly use a date enclosed with double quotation marks

Press Enter key to get the first result, then drag auto fill handle down to get other results.

Explanation

TODAY function: returns to the date of today.

EOMONTH function: gets the last day of month n months in future or past.

AND function: tests multiple conditions and returns TRUE or FALSE.

Here the formula is divided into two parts:

date>EOMONTH(TODAY(),-(6+1)) checks if the date is last 6 months from today;

date<=EOMONTH(TODAY(),-1) checks if the month of the date is equal to or later than today.

While both parts return TRUE, the whole formula returns TRUE, or it returns FALSE.

Relative Formulas

• Get midpoint of two dates
  Here this article introduces two formulas to get the midpoint of two dates in Excel in two different situations.
• Get number of worday between two dates
  To count the number of workdays between two dates, the NETWORKDAYS function in Excel can help.
• Get quarter from given date
  This tutorial provides formulas for you to get quarter from given dates in Excel.
• Get same day last year or next year
  In this tutorial, it provides two formulas to separately get the same date last year and next year based on the given date in Excel.

Relative Functions

• Excel DATE Function
  Create date with year, month and day
• Excel YEAR Function
  Returns the year of date in 4-digit serial number format
• Excel MONTH Function
  The MONTH is used to get the month as integer number (1 to 12) from date
• Excel DAY Function
  DAY function gets the day as a number (1 to 31) from a date
• Excel WORKDAY Function
  Add workdays to the given date and returns a workday