Problems Solved
How do I fix my Excel formula?
QI’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!
AExcel 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. With that in mind, it helps to understand the make-up of any Excel formula if one first considers the problem in plain English.
In effect, then, you want to tell Excel that it should first look at the contents of DOB cell (A2 in our example, see screenshot 1 ). If it’s blank, then it should display a ‘No data’ message in cell B2. However, if A2 is not blank and instead contains a DOB, then you’d like it to calculate and display the years and months from that entered date. So, that’s the if/or decision that needs to be turned into a formula using the =IF function.
Now, remember that we said you were already most of the way there? That’s because your existing formula will work perfectly for the second half of this =IF function. The first half, then, simply needs to tell Excel to look in cell A2 and, if blank, enter ‘No data’ into B2. The =F function is structured with the logical test first (checking the contents of cell A2), then a comma followed by what to do if it’s blank (type ‘No data’), and then a second comma followed by what to do if it’s not data (execute your existing formula).
With all that spelled out in English, the logic of the following formula – while it is undoubtedly a bit complex – should hopefully make sense, and is what you need for the desired result: =IF(A2="","NO DATA",DATEDIF(A2,C2, "Y") & "." & DATEDIF(A2,C2,"YM")). Because the width of our columns mean this long formula will be hard to get just right, we’ve put it on a Pastebin page at www.snipca.com/37254, so you can just copy and paste it into your sheet 2 .