The sample dataset showcases the End Dates of different projects.
To see if the End Date is within 3 months from the Current Date:
Method 1 – Apply the Excel EDATE Function to Find If a Date Is Within 3 Months
Use the EDATE function , the AND function and the TODAY function.
1.1 Within the Next 3 Months
STEPS:
- Select D5 and enter the formula below:
=AND(C5>TODAY(),C5<=EDATE(TODAY(),3))
In the formula, the AND function is used:
- Logic 1 declares C5 is greater than the date of today. The TODAY function returns today’s date.
- Logic 2 denotes C5 should be less than or equal to the date of 3 months from today. The EDATE function returns the serial number of a date.
- Press Enter and drag the Fill Handle down to see the results.
The End Date of Project 2 is within the next 3Â months.
1.2 Within the Last 3 Months
STEPS:
- Select D5 and enter the formula below:
=AND(C5<TODAY(),C5>=EDATE(TODAY(),-3))
Logic 1 declares C5 should be less than the date of today and Logic 2 denotes C5 should be greater than or equal to the date of the last 3 months from today. To calculate the last 3 months, the minus (–) sign is used.
- Press Enter.
- Drag down the Fill Handle to see the result in the rest of the cells.
Method 2 – Check If a Date Is Within 3 Months Using the Excel EOMONTH Function
The EOMONTH function returns the serial number of the last day of a month.
2.1 Within the Next 3 Months
STEPS:
- Select D5 and enter the formula below:
=AND(C5>EOMONTH(TODAY(),0),C5<=EOMONTH(TODAY(),3))
Combine the EOMONTH and TODAY functions:
- EOMONTH(TODAY(),0): denotes today’s date.
- EOMONTH(TODAY(),3): indicates the last date of the 3rd month from today.
- Press Enter.
- Drag down the Fill Handle to see the result in the rest of the cells.
2.2 Within the Last 3 Months
- Select C5 and enter the formula below:
=AND(C5<EOMONTH(TODAY(),0),C5>=EOMONTH(TODAY(),-3))
When compared to the previous one, in this formula a minus (–) was added before 3 and the greater than and less than symbols were changed.
- Press Enter.
- Drag down the Fill Handle to see the result in the rest of the cells.
Method 3 – Combine the YEAR, DATE & MONTH Functions to Find If a Date Is Within 3 Months
Combine the YEAR, DATE, and MONTH functions. Enter the current date in a cell. Here, C11.
3.1 Within the Next 3 Months
Use the AND, TODAY, DATE, YEAR, MONTH, and DAY functions. The DATE function expresses a date in the yyyy/mm/dd format.
STEPS:
- Select D5 and enter the formula below:
=AND(C5>TODAY(),C5<=DATE(YEAR($C$11),MONTH($C$11)+3,DAY($C$11)))
The output of DATE(YEAR($C$11),MONTH($C$11)+3,DAY($C$11)) is 11/28/2022, which compares 8/28/2022.
- Press Enter.
- Drag down the Fill Handle to see the result in the rest of the cells.
3.2 Within the Last 3 Months
- Select D5 and enter the formula below:
=AND(C5<TODAY(),C5>=DATE(YEAR($C$11),MONTH($C$11)-3,DAY($C$11)))
The conditions check if a date is within the last 3 months from today.
- Press Enter.
- Drag down the Fill Handle to see the result in the rest of the cells.
Method 4. Select a Specific Value If the Date Is Within 3 Months Using the Excel IF Function
STEPS:
- Select D5 and enter the formula below:
=IF(AND(C5>TODAY(),C5<=EDATE(TODAY(),3)),"Within 3 Months","X")
If the conditions are true, the output of the formula will be Within 3 Months. Otherwise, it will show X. The formula is the same as the one used Method 1 in the first argument.
- Press Enter.
- Drag down the Fill Handle to see the result in the rest of the cells.
- To check if a date is between two specific dates, use the formula below.
=IF(AND(C5>DATE(2022,7,1),C5<=DATE(2022,10,1)),"Within 3 Months","X")
The formula checks if a date is between 7/1/2022 and 10/1/2022.
Method 5 – Use Conditional Formatting to Highlight Dates Within 3 Months
Use the formula of Method 1.
STEPS:
- Select C5:C9.
- Go to the Home tab and select Conditional Formatting.
- Select New Rule.
- In the New Formatting Rule box, select ‘Use a formula to determine which cells to format’ in Select a Rule Type.
- Enter the formula in ‘Format Values where this formula is true’ :
=AND(C5>TODAY(),C5<=EDATE(TODAY(),3))- Click Format.
- In the Format Cells window, click Fill and choose a color in Background Color.
- Click OK.
- Excel will highlight cells that meet the conditions.
Download Practice Book
Download the practice book.
<< Go Back to Dates | Compare | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
