Help needed on nested IF statements dealing with dates

**jaacm** · 01-31-2014, 07:02 PM

I need to complete a calculation based on 2 different dates. The two dates will be different. One will be a hire date, and one will be todays date. If the hire date is 4 years plus a day earlier than today’s date I need to have a calculation based on accrual rate of 14 hours per month. If the hire date is 10 years plus a day earlier than today’s date, then a calculation will be based on an accrual rate of 18 hours per month. Otherwise the accrual rate is based on 10 hours per month. The catch is this rate may change in the middle of the year. If the hire date changes in June and the year is also equal to either 4 or 10 past then, I will need an accrual rate of one amount for the months of Jan through June and then the additional rate will be used for July through Dec. The 15th of the month will be the deciding factor for if the month counts in the original accrual rate or a new accrual rate. If the hire date is before the 15th, then the new accrual rate will take effect, but if the date is the 15th or after the new accrual will start in the following month.

I currently have:
IF(TODAY()<DATE(YEAR(B136)+4,MONTH(B136),DAY(B136)+1),10,IF(TODAY()<DATE(YEAR(B136)+10,MONTH(B136),DAY(B136)+1),13.333333,16.666666))

This calculates correctly, but does not take into account the different rate for each month. I am not sure how the best way to go about this would be. Making 12 columns and have a calculation based on the year and month (with day taken into account) or a HUGE nested if statement.
I modified the above statement to the following (just to start), but it only returns a FALSE, so I have something out of place. Been looking at it so long, I swear there is nothing wrong.

IF(TODAY()=DATE(YEAR(B145)+4,0,0),IF(TODAY()>DATE(0,MONTH(B145),0),(G145)=DATE(MONTH(B145)*13.333333,0,0),(G145)=DATE(MONTH(B145)*10,IF(TODAY()<DATE(0,MONTH(B145),0),(G145)=DATE(MONTH(B145)*10,0,0)),)))

In summary: Today = 01/26/2014. If hire date = 1/20/2010 then the month of Jan, will accrual at a rate of 10 hours, and Feb. through Dec. will accrual at a rate of 14 hours per month, for a total of 164 hours in the year. If hire date = 4/6/2004 then the months of Jan, Feb, March, and April will accrual at the 14 hours a month rate, and May – Dec will accrual at a rate of 18 hours a month. For a total of 200 hours.

Any and all help, or suggestions, would be HUGELY appreciated!!
Thank you in advance - Amy

**6StringJazzer** · 01-31-2014, 10:30 PM

This type of problem is most typically managed with a table showing what accrual rates for how many years of service, rather than trying to jam it all into a monster formula. As you can see, that type of formula gets unwieldy and God help you if company policy ever changes.

To post a file:

Under the text box where you type your reply click the Go Advanced button.
In the next screen look above the reply box and click the "paper clip" icon. You will get a pop-up screen.
In the pop-up, click Add then Select to select a file from your local drive
Click the Upload button to upload the file
Click Done to attach it.

It will be displayed as an attachment underneath your post text. Alternatively as the last step you can click the Insert Inline button and a hyperlink to the file will be inserted directly into your post text in the spot where your cursor is set at the moment you click the button.