It was a dark early start this morning, and I had my long walk to the bus to begin the day with. Work was really good today, and as an extra geeky treat and I can show the work I did today, well the final formula I constructed in order to solve a tricky conundrum within Excel. Here’s the problem I was required to solve. I have an Excel sheet that records dates and times in single cells, I was tasked with finding a way of calculating the exact difference between different sets of dates. Yeah I know sounds really easy doesn’t it 🙂
Hahaha and that’s when you run into the issue of Excel trying to calculate dates and times together. Sure it can do them individually but put them together and it’s a proper shit storm. Now you may think that just a simple concatenation to pull out the time aspect ad calculate it separately may work, but again this has pitfalls, and besides I had about 30 pairs of columns to run this against. So I set myself the challenge of constructing one formula to rule them all. Oh and I should point out the that it must calculate based on working days as weekends don’t count toward the total, and to further complicate the task, human error when inputting the data needs to be highlighted, as there are many examples of end dates being before start dates – yep we deal with lots of time travel devices at work, the administration involved is phenomenal!
Right here is the formula to rule them all, and in the darkness bind them:

IF(ISERROR(INT((IF(OR(J3=0,G3=0),0,(IF(J3-G3=0,0,((IF((J3-G3)<=7,(J3-(IF(WEEKDAY(G3,2)=6,G3+2,(IF(WEEKDAY(G3,2)=7,G3+1,G3))))),(IF((J3-G3)<=14,(J3-(IF(WEEKDAY(G3,2)=6,G3+4,(IF(WEEKDAY(G3,2)=7,G3+3,G3+2))))),(IF((J3-G3)<=21,(J3-(IF(WEEKDAY(G3,2)=6,G3+6,(IF(WEEKDAY(G3,2)=7,G3+5,G3+4))))),(IF((J3-G3)<=28,(J3-(IF(WEEKDAY(G3,2)=6,G3+8,(IF(WEEKDAY(G3,2)=7,G3+7,G3+6))))),(J3-G3)))))))))))))))&" Days "&INT(MOD((IF(OR(J3=0,G3=0),0,(IF(J3-G3=0,0,((IF((J3-G3)<=7,(J3-(IF(WEEKDAY(G3,2)=6,G3+2,(IF(WEEKDAY(G3,2)=7,G3+1,G3))))),(IF((J3-G3)<=14,(J3-(IF(WEEKDAY(G3,2)=6,G3+4,(IF(WEEKDAY(G3,2)=7,G3+3,G3+2))))),(IF((J3-G3)<=21,(J3-(IF(WEEKDAY(G3,2)=6,G3+6,(IF(WEEKDAY(G3,2)=7,G3+5,G3+4))))),(IF((J3-G3)<=28,(J3-(IF(WEEKDAY(G3,2)=6,G3+8,(IF(WEEKDAY(G3,2)=7,G3+7,G3+6))))),(J3-G3)))))))))))))),INT((IF(OR(J3=0,G3=0),0,(IF(J3-G3=0,0,((IF((J3-G3)<=7,(J3-(IF(WEEKDAY(G3,2)=6,G3+2,(IF(WEEKDAY(G3,2)=7,G3+1,G3))))),(IF((J3-G3)<=14,(J3-(IF(WEEKDAY(G3,2)=6,G3+4,(IF(WEEKDAY(G3,2)=7,G3+3,G3+2))))),(IF((J3-G3)<=21,(J3-(IF(WEEKDAY(G3,2)=6,G3+6,(IF(WEEKDAY(G3,2)=7,G3+5,G3+4))))),(IF((J3-G3)<=28,(J3-(IF(WEEKDAY(G3,2)=6,G3+8,(IF(WEEKDAY(G3,2)=7,G3+7,G3+6))))),(J3-G3))))))))))))))))*24)&" Hours and "&MINUTE((IF(OR(J3=0,G3=0),0,(IF(J3-G3=0,0,((IF((J3-G3)<=7,(J3-(IF(WEEKDAY(G3,2)=6,G3+2,(IF(WEEKDAY(G3,2)=7,G3+1,G3))))),(IF((J3-G3)<=14,(J3-(IF(WEEKDAY(G3,2)=6,G3+4,(IF(WEEKDAY(G3,2)=7,G3+3,G3+2))))),(IF((J3-G3)<=21,(J3-(IF(WEEKDAY(G3,2)=6,G3+6,(IF(WEEKDAY(G3,2)=7,G3+5,G3+4))))),(IF((J3-G3)<=28,(J3-(IF(WEEKDAY(G3,2)=6,G3+8,(IF(WEEKDAY(G3,2)=7,G3+7,G3+6))))),(J3-G3)))))))))))))))&" Minutes")=TRUE,"0",(INT((IF(OR(J3=0,G3=0),0,(IF(J3-G3=0,0,((IF((J3-G3)<=7,(J3-(IF(WEEKDAY(G3,2)=6,G3+2,(IF(WEEKDAY(G3,2)=7,G3+1,G3))))),(IF((J3-G3)<=14,(J3-(IF(WEEKDAY(G3,2)=6,G3+4,(IF(WEEKDAY(G3,2)=7,G3+3,G3+2))))),(IF((J3-G3)<=21,(J3-(IF(WEEKDAY(G3,2)=6,G3+6,(IF(WEEKDAY(G3,2)=7,G3+5,G3+4))))),(IF((J3-G3)<=28,(J3-(IF(WEEKDAY(G3,2)=6,G3+8,(IF(WEEKDAY(G3,2)=7,G3+7,G3+6))))),(J3-G3)))))))))))))))&" Days "&INT(MOD((IF(OR(J3=0,G3=0),0,(IF(J3-G3=0,0,((IF((J3-G3)<=7,(J3-(IF(WEEKDAY(G3,2)=6,G3+2,(IF(WEEKDAY(G3,2)=7,G3+1,G3))))),(IF((J3-G3)<=14,(J3-(IF(WEEKDAY(G3,2)=6,G3+4,(IF(WEEKDAY(G3,2)=7,G3+3,G3+2))))),(IF((J3-G3)<=21,(J3-(IF(WEEKDAY(G3,2)=6,G3+6,(IF(WEEKDAY(G3,2)=7,G3+5,G3+4))))),(IF((J3-G3)<=28,(J3-(IF(WEEKDAY(G3,2)=6,G3+8,(IF(WEEKDAY(G3,2)=7,G3+7,G3+6))))),(J3-G3)))))))))))))),INT((IF(OR(J3=0,G3=0),0,(IF(J3-G3=0,0,((IF((J3-G3)<=7,(J3-(IF(WEEKDAY(G3,2)=6,G3+2,(IF(WEEKDAY(G3,2)=7,G3+1,G3))))),(IF((J3-G3)<=14,(J3-(IF(WEEKDAY(G3,2)=6,G3+4,(IF(WEEKDAY(G3,2)=7,G3+3,G3+2))))),(IF((J3-G3)<=21,(J3-(IF(WEEKDAY(G3,2)=6,G3+6,(IF(WEEKDAY(G3,2)=7,G3+5,G3+4))))),(IF((J3-G3)<=28,(J3-(IF(WEEKDAY(G3,2)=6,G3+8,(IF(WEEKDAY(G3,2)=7,G3+7,G3+6))))),(J3-G3))))))))))))))))*24)&" Hours and "&MINUTE((IF(OR(J3=0,G3=0),0,(IF(J3-G3=0,0,((IF((J3-G3)<=7,(J3-(IF(WEEKDAY(G3,2)=6,G3+2,(IF(WEEKDAY(G3,2)=7,G3+1,G3))))),(IF((J3-G3)<=14,(J3-(IF(WEEKDAY(G3,2)=6,G3+4,(IF(WEEKDAY(G3,2)=7,G3+3,G3+2))))),(IF((J3-G3)<=21,(J3-(IF(WEEKDAY(G3,2)=6,G3+6,(IF(WEEKDAY(G3,2)=7,G3+5,G3+4))))),(IF((J3-G3)<=28,(J3-(IF(WEEKDAY(G3,2)=6,G3+8,(IF(WEEKDAY(G3,2)=7,G3+7,G3+6))))),(J3-G3)))))))))))))))&" Minutes"))

And just in case anyone asks, I tried to use Networkdays, but the time element involved caused too many issues with it, the results displays rather neatly as "xx"Days "xx"Hours "xx"Minutes. It was the most awesome formula I've created. Sadly all of the other Excel geeks were off today, so I was denied the rightful worship I was due 😀

After all that I then went and discovered this online. It’s the awesome Joe Abercrombie’s “The Blade Itself” in comic form, if you have never had the pleasure of following the exploits of Logan Ninefingers, then I’d recommend you have a read. The only slight down sight is that, to me, the characters all look a little too clean. When I read the books I didn’t have them quite as well groomed or clean cut. But it’s still pretty darn good, enjoy 🙂

Odi et amo………..Excrucior

Stay Slinky People,
TTFN

Malinari..
Poisoner extraordinaire

Advertisements