Excel Formula

[rwalker]

I was wondering if anyone has ever wrote an excel formula that calculates vacation days based on an anniversary date with possible rollover days?

I tried everything I can think of. I'm using example below. I need to take the (Ann.Date) compare it the current date, and determine if there should be a vacation allotment an if so then add it to (days remaining until 12/31), then place that value the last column (Total Days Remaining for Calendar Year).

If the Ann. Date has not been obtain yet then i need to reference (Days Remaining) in the last column (Total Remaining for the Calendar Year). I would appreciate guidance or advice that anyone can give.

TX
Rob

Anniversay Date Shift Vacation Allotment Days Taken Days Remaining until 12/31 Total Remaining for the Calendar Year 2/14/1982 0 15 0 15 30

 

[EvanDavis]

Welcome to the forum


Take a look HERE and see if that is what you are after.

 

[rwalker]

Not quite, I already have a formula to provide the correct number of vacation days, I need one that adds the value into an existing balance once the anniversary date has been obtain on a yearly basis and then add in any unused vacation days to it.

TX

 

[EvanDavis]

Not quite, I already have a formula to provide the correct number of vacation days, I need one that adds the value into an existing balance once the anniversary date has been obtain on a yearly basis and then add in any unused vacation days to it.

TX

Post your formula. Alow is online at the moment, he might be able to shed some light.

 

[rwalker]

=if(day(b3)>=day($n$2),(month(b3)>=(month($n$2), d3+f3,f3)
$n$2=current date
b3= anniversary date
d3= vacation allotment
f3= days remaining

 

[V_R]

One thread only please.

 

[rwalker]

I new at this, I did realize I had two going.

 

[V_R]

Not a problem.

 

[alow]

Evan must have caught this just after I left the site (I leave it open at work so I'm sort of always showing "online" )
The problem with your IF statement is that to measure a date the way you are trying to, you would also need to use an AND formula in the beginning as follows:

=IF(AND(DAY(B3)>=DAY($N$2),(MONTH(B3)>=(MONTH($N$2),D3+F3,F3)

You could also simplify it (maybe?) by replacing the $N$2 with TODAY() as follows here:

=IF(AND(DAY(B3)>=DAY(TODAY()),(MONTH(B3)>=(MONTH(TODAY()),D3+F3,F3)

This 'simplification' really just means that you don't need a static cell with the current date. Let me know if there are any syntax errors as I'm writing these on the site, and haven't tried them in Excel. Good Luck!

 

[rwalker]

ALOW

Thanks, I had previously tried a "if/and" statement and it did not work for me. I tried your code and got an error stating, " the formula you wrote contains an error". Let me know if you have any other ideas, I really appreciate your suggestions to this point. It has got to be something simple, that's why I'm not seeing it!!!!

TX
Rob

 

[alow]

It's what I get for being lazy. Problems with Parentheses. Try this one:

=IF(AND(DAY(B3)>=DAY($N$2),MONTH(B3)>=MONTH($N$2)),D3+F3,F3)

 

[rwalker]

Almost!
Current code:
if(and((day(b3)>= ($n$2)),(month(b3)>= (month($n$2))), d3+f3,f3)

I get an answer of 15, but the correct answer should be 30.
Numerical values assigned:
D3= 15
f3= variable as to number of days already taken, in this particular case it is 15.

 

[alow]

I think you may need to change the > to a <, in that case. What it is currently doing is looking to see if the anniversary date has already passed this year. IF the anniversary date has passed (is earlier in the year than the current date), then the formula will taking only what is in F3. If the anniversary date has not passed, then the formula will be taking F3+D3. See if that clears it up.

 

[rwalker]

I have been down this road also, its does not distinguish between current date and anniversary date, therefore it automatically adds pending vacation time whether it is actually accrued or not.

 

[alow]

I'm going to need further explanation. I don't understand what you're getting at, here.

 

[rwalker]

The idea is that every year the vacation days should reload on the anniversary date and add to whatever unused days that are left within that calendar year to yield a total year end bucket. However I assuming that my formula does not recognize either the current or month, because the only answers i get are either "error" or the number 15.

Once again thanks for all of your help!!