Forum Discussion
Compare dates not working correctly
I am trying to compare two date sets and if the Age column is greater than the $X$2 (Age Max) column than place an X in an X Value cell. It almost works but if the month is 10, 11, or 12 it only looks at the first digit "1" and if the $X$2 cell are months 2-9 they are considered greater and so the X value is not placed. How can I get this formula to verify the date is greater than or less than correctly? Here is my formula as well as an example of the scenario.
=IF([@Age]>$X$2,"X","")
Example:
| Birthdate | AGE | X VALUE | AGE MAX ($X$2) | |
| 1/29/2021 | 0 Years, 11 Months | (Should have an X in this scenario) | 0 Years, 6 months | |
| Formula | =DATEDIF(D16,$U$2,"Y")&" Years, "&DATEDIF(D16,$U$2,"YM")&" Months" | =IF([@Age]>$X$2,"X","") | =DATEDIF(W2,$U$2,"Y")&" Years, "&DATEDIF(W2,$U$2,"YM")&" Months" |
6 Replies
In the formula '=IF([@Age]>$X$2,"X","")', I see that both [@Age] and [@Age Max] has text in the value. It is only natural that excel would fail to compare logically.
I would advice not to do what you did in [@Age] and [@Age Max] cells.
Instead, try the following:
1. IF([@Age]>$X$2,"X","") # Keep this formula in [@X Value] as it is or change as required
2. TODAY()-[@Birthdate] # Use this for [@Age]. To show in years and months, set the custom cell/column format to: yy "Years and" mm "Months"
3. For [@Age Max], try to define in terms of Excel date: dd/mm/year. for example Max 0 years and 6 months would be 1/6/0 # To show in years and months, set the custom cell/column format to: yy "Years and" mm "Months"- Or use: yy "Years," mm "Months"
to show: 2 Year, 2 Months format.
Thank you but still did not solve the issue, when it reaches the month it still treats 2-9 as greater than 10-12. I suspect it is only viewing the first digit.
You should use the dates in the formula, not the text values formatted as "m years n months".
