As software developers, we're seen as by the world as the Experts of All Things Computer. Want more RAM installed? Of course we can help, we know C++! Blue screen of death? Our day job calls for .NET, so we know exactly what to do! Need a monitor plugged in? That's basically the same thing as programming!

Fortunately, our hard-earned skills of “reading an instruction manual” and “applying common sense” enable us to solve most of these vexing computer mysteries. But when it comes to anything more advanced than rudimentary, we tend to be as lost as our laymen counterparts. Take, for example, that little paintbrush-looking button in Excel. Did you know it's called the “Format Painter” and can magically copy/paste cell formatting? Did you even know it was there? I didn't until recently, and holy crap is it awesome.

Of course, since we're the Experts of All Things Computer, when a computer conundrum comes our way, we have to address it. Especially when the call for help comes from an attractive member of the opposite sex. And that's exactly how Alex W. found himself staring at the contents of Cell 24Z.

“My spreadsheet doesn't seem to work anymore,” the cute brunette in accounting told him, “It does have a few complicated formulas, but I'm sure you can figure them out.”

Expecting a SUM here and a COUNTIF there, Alex obliged. It was just an Excel spreadsheet, after all. How hard could it possibly be?

“I did make a small change to 24Z a while back,” the accountant added, “but for the life of me, I can't figure out why it's not matching up billing terms.”

“Matching up” was not a phrase that Alex associated with Excel formulas, and assumed she meant IIF or COUNT. “Let's see what the problem is,” Alex confidently replied just before he clicked on Cell 24Z and saw the following.

=DATE(YEAR(H$1),MONTH(H$1)+CHOOSE(MATCH(F$1,{"5Y","Y","H/Y","5M","4M","Q","B
i-M","M","4W","F","W","D","365D","90D","30D","12W","13P","26W","T"},0),61,13
,7,6,5,4,3,2,0,0,0,0,0,0,0,0,0,0,IF(H$1<=FLOOR(DATE(YEAR(H$1),5,DAY(MINUTE(Y
EAR(H$1)/38)/2+56)),7)-34,8,13)+IF(MONTH(H$1)<8,0,MONTH(FLOOR(DATE(YEAR(H$1)
+1,5,DAY(MINUTE(((H$1-1)/365.25+1900)/38)/2+56)),7)-37)-1)-MONTH(H$1)),CHOOS
E(MATCH(F$1,{"5Y","Y","H/Y","5M","4M","Q","Bi-M","M","4W","F","W","D","365D"
,"90D","30D","12W","13P","26W","T"},0),0,0,0,0,0,0,0,0,DAY(H$1)+(6-MOD(H$1,7
))+28,DAY(H$1)+(6-MOD(H$1,7))+14,DAY(H$1)+(6-MOD(H$1,7))+7,DAY(H$1)+1,DAY(H$
1)+365,DAY(H$1)+90,DAY(H$1)+30,DAY(H$1)+(6-MOD(H$1,7))+84,DAY(H$1)+(6-MOD(H$
1,7))+364,DAY(H$1)-(6-MOD(H$1,7))+182,IF(MONTH(H$1)<8,0,DAY(FLOOR(DATE(YEAR(
H$1)+1,5,DAY(MINUTE(((H$1-1)/365.25+1900)/38)/2+56)),7)-37))))

“Oh” was the only word that came out when Alex saw that formula. “Yeah, this may take a minute.”

And actually, it took more than a minute. After spending a few minutes reading about how CHOOSE and MATCH worked, he spent the rest of the afternoon “debugging” the spreadsheet. He eventually figured it out, thus maintaining his position as an Expert of All Things Computer.

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!