'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Use an Excel Formula to Return Yes or No for Over 18 and Over 21 Ages
Question details
The user needs to compare a date of birth against the current date to determine if a person is over 18 or over 21, outputting a 'Yes' or 'No' text string.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Filtering or evaluating eligibility based on age thresholds (like 18 or 21) using birth dates imported from Microsoft Forms or entered manually.
- Observed behavior
- Return the exact string 'Yes' if the calculated age meets or exceeds the specified threshold, and 'No' otherwise.
Ensure that your Date of Birth column is formatted as actual Dates in Excel, especially if the data was imported from external sources like Microsoft Forms or Power Automate.
Use IF and DATEDIF Functions for Exact Age Calculation
The DATEDIF function accurately calculates the elapsed years between the date of birth and today, which is the most reliable method for calendar-accurate age comparisons.
This method avoids issues with leap years by calculating exact full years elapsed.
Click on the cell where you want the 'Yes' or 'No' result to appear (e.g., cell B2).
Assuming the Date of Birth is in cell A2, type the following formula: =IF(DATEDIF(A2,TODAY(),"Y")>=18,"Yes","No") and press Enter.
If you are checking for an age of 21, adjust the threshold in the formula to: =IF(DATEDIF(A2,TODAY(),"Y")>=21,"Yes","No").
Use EDATE for Excel Table Structured References
If your data is formatted as an official Excel Table, using structured references and the EDATE function makes formulas easier to read and automatically expand.
Troubleshoot #NAME? Errors in the Formula
Sometimes importing data or having mismatched regional settings will cause the formula to break and return a #NAME? error.
Calculate Ages and Manage Formulas Seamlessly in WPS Spreadsheet
You can effortlessly build age calculation formulas like IF, DATEDIF, and EDATE using WPS Spreadsheet. It offers full compatibility with standard spreadsheet syntax, ensuring imported forms evaluate correctly.
- 1. Open your data in WPS Spreadsheet: Launch WPS Office and open your spreadsheet containing the dates of birth.
- 2. Select your output cell: Click on the cell adjacent to the first date of birth where the eligibility status will go.
- 3. Insert the DATEDIF formula: Type =IF(DATEDIF(A2,TODAY(),"Y")>=18,"Yes","No") and press Enter on your keyboard.
- 4. Auto-fill the column: Drag the fill handle down to apply the Yes/No logic to all other entries in your list.
Frequently Asked Questions
Why does my formula return a #NUM! error?
The DATEDIF function returns a #NUM! error if the date in the starting cell (Date of Birth) is greater than the ending date (Today). Check your dataset to ensure there are no future dates or invalid entries.
Can I use an approximate calculation like 18 * 365.25?
Yes, you can use =IF(TODAY()-A2>=18*365.25,"Yes","No"). However, this is an approximation and might be off by a day during certain leap year cycles. The DATEDIF or EDATE methods are highly recommended for strict legal or calendar accuracy.
Why is the formula returning 'No' for everyone regardless of their age?
This usually happens if Excel doesn't recognize your imported dates as actual dates, treating them as text instead. You can fix this by selecting your dates column, going to the Data tab, and using the 'Text to Columns' tool to convert them.
How do I add a third option for ages between 18 and 21?
You can nest multiple IF statements. For example: =IF(DATEDIF(A2,TODAY(),"Y")>=21,"Over 21", IF(DATEDIF(A2,TODAY(),"Y")>=18,"Over 18","Under 18")).
