Hi - Dave here.
Happy Friday!
This week, I have an example of an older formula I reworked recently to simplify and streamline the logic using some tricks I've learned over the years.
The formula is simple; it marks dates that share the same month and year. The original formula looks like this:
=IF(AND(YEAR($B$5)=YEAR(D5),MONTH($B$5)=MONTH(D5)),"x","")
The improved formula looks like this:
=IF(TEXT($B$5,"mmyyyy")=TEXT(D5,"mmyyyy"),"x","")
Notice that we've cut the number of function calls in half. The original formula needs six, while the improved formula needs just two.
[
Download the workbook and read the full explanation]
This is a good example of how a better understanding of Excel's functions can help you create better formulas. The improved formula is more compact, easier to read, and easier to extend. The article above has a complete explanation. Download the worksheet to see how it works.
