Problems Solved
Email us your problem and we’ll try to help: noproblem@computeractive.co.uk
PROBLEM OF THE FORTNIGHT
How do I fix my Excel formula?
Q I’m struggling with an Excel formula. I want to return the number of years and months from a given date of birth (DOB), or the message ‘No data’ if the DOB cell is blank. I’m using the formula =DATEDIF(A2,C2,"Y") & "." & DATEDIF(A2,C2,"YM"), which presents the relevant years and months separated by a dot. However, if the DOB cell is blank I get a number over 100. I believe this is because it counts from a default of the first day of the year 1900. Your excellent magazine is famous for its no-jargon approach, so hopefully your reply will be understood by this silver surfer!
Daniel Tilcock
A Excel formulas can be incredibly powerful but equally as complex to understand and create. Moreover, there are usually multiple ways to achieve the same result.
The good news here is that you were most of the way to achieving what you want. Your existing formula can be easily tweaked to deliver the ‘No data’ message for a blank DOB cell. The existing string, which is a formula that in both your and our example sits in cell B2, was =DATEDIF(A2,C2,"Y") & "." & DATEDIF(A2,C2,"YM"), where A2 contained the DOB and C2 contained today’s date (itself inserted and updated automatically using the =TODAY() function).
The trick is to ‘nest’ your existing formula within a new formula that makes use of the =IF function, because this tells Excel to make an if/or decision before doing something else. That sounds simple enough but actually understanding how to construct such a formula can hurt one’s head.
