20070116

my new job is so exciting!

So I have a new job.even my desk is exciting I started on jan 2nd & I'm working with defined benefit pension plans again. They hired me 2 check other people's work. Yes, all I do is work with #'s all day, usually in Excel.They gave me a spreadsheet to use but I got an error because it assumed everyone was going 2 quit on the last day of the month. Because, of course, everyone waits until the last day of the month 2 quit, uh-huh, sure.

When I figured out a better way of calculating the service I was all: "Yes! U can't beat me, u bloated Excel formula. Take that!" It makes me want 2 slam the keyboard down in victory & swagger away from my cubicle high-fiving people. Excel: pwned! I'm a geek & when I figure out a more elegant Excel function to use I suddenly have an exciting day. I wanted 2 tell someone "look at what I did" but then I realized that nobody there would b as excited as I was. Read on if u dare!

Their system calculates dates 3 different ways at different points in the calculation, which apparently, only I noticed. To determine the credited service for the years after 2006 they used this old formula:

=ROUND(IF(YEAR(B1)<YEAR(NOW()),0,(VLOOKUP(IF(MONTH(B2)=1,13,MONTH(B2)),tables!A1:D12,4)+IF(AND(YEAR(B2)=2008,MONTH(B2)<>2),1,0))/(365+(IF(AND(YEAR(B2)=2008,MONTH(B2)<>2),1,0)))+B3),4)

Which didn't take into account a termination date in the middle of the month, so it produced the wrong amount. My new formula is not only shorter (because it avoids the lookup table & the workaround for the 2008 leap year) it also allows for weird termination dates:
=IF(YEAR(B1)<2006,0,ROUND(DATEDIF(DATE(YEAR(B1),1,0),B1,"d")/DATEDIF(DATE(YEAR(B4),1,0),DATE(YEAR(B1),12,31),"d")+B3,4)

Yearfrac sucks! Yeah son! Don't hate!

0 Comments:

Post a Comment

Links to this post:

Create a Link

<< Home