I was given the responsibility of tracking vacation time accrual when I started this position. Since there was no system in place to do so, I went about building one. The formula I'm currently working on is the last piece of the puzzle.



Basically, I’m trying to set up a way for this spreadsheet to account for Vacation time carryover. I am attempting to accomplish this by using an IF formula. Column F is a “ticker” that subtracts the Column C(modified version of the person’s start date) from Column G(today’s date) to produce how many days into their Anniversary year they are in, to accurately track their accrual.


I’ve come up with several different formulas, and it seems that some are turning up incorrect values.

excelscreenshot.png


In the screenshot above, you’ll see the formula in cell D17 indicates that if F17<=365, return the value of F17, but if false, return F17-365.

The problem is, cell F17 IS less than or equal to 365, it’s 319, which means the value it should spit out is 319, and not -49.

In a later incarnation of this spreadsheet, I tried adding an extra column with the number 365... see below:
excelscreenshot.png

The formulae are still producing false positives. See the screenshot. The formula I used, =IF(G13<=F13,G13, G13-F13), worked in cell D13,where G13 was not in fact less than or equal to 365. But just below it, in cell D14, you’ll notice that G14 IS less than or equal to 365. Yet excel is telling me that it’s not. Any thoughts?