Vacation Accrual

[melindaladd]

Hello, I have been trying to figure this out for days it is time to move on to a new chapter in my life. Could someone please see if they have any advice? <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I am trying to figure out how to accrue vacation time in excel. I have tried all sorts of vlookup's, if's and date functions, and because my work is so particular about when the vacation time is accrued it is very difficult (for me anyways) to figure out.<o></o>
<o></o>
If someone could help me figure out how to write the following in excel I would be so grateful. <o></o>
<o></o>
On the one year anniversary an employee accrues 40 hours of vacation. After that point, for the months of Feb. through November they receive an additional 8 hours on the last day of each of those months (if an employee quits on the day before the last day or sometime in December or January they do not get the hours, so it is very imperative it accrues on this last day and only in these months) for the rest of their employment. <o></o>
<o></o>
I was a little amused and admittedly had a bit of a superiority complex for a moment when I learned they were still doing this by hand- I now understand why! Thank you in advance if you have any advice- I am eternally grateful! <o></o>

 

[Simon Lloyd]

How is your worksheet set out? do you want a formula or vba solution?
If the date to go by was in A1 then this formula will show a value depending on whether the date in A1 mtches the last date for that month:

=IF(DAY(A1)<>DAY(DATE(YEAR(A1),MONTH(A1)+1,0)),0,8)

 

[melindaladd]

Thank You Simon, for your quick response and help on this!

I thought a formula would be the easiest and most straight forward solution to this?

The speadsheet is set up pretty straight forward, as follows:

A1 Employee
B1 Employee Number
C1 Start Date
D1 Today ()

So I was hoping to:
1. Add 40 hours on the 1 yr. aniversary from C1.
2. Add 8 hours on the last day of every month (with the exception of December and January) from C1 through D2.


Does that make any sense? Is it possible?

I cannot thank you enough for your attention and time on this!

 

[Simon Lloyd]

If thats the set up then you can't use a formula really, are you opposed to using more columns?

 

[Simon Lloyd]

In fact the simplest method for your set up would be:
=DATEDIF(C3,D3,"m")*8/24 & " Days"
The formula counts whole months, multiplied by 8 (your hours) and divided by 24 to give the number of days, format your cell as number to zero decimal places and you should be good to go (i think!).

 

[Simon Lloyd]

In fact just gave it another tweak:
=ROUNDDOWN(DATEDIF(C3,D3,"m")*8/24 + DATEDIF(C3,D3,"y")*40/24,0) & " Days"
This formula adds 40 hours for every 12 months passed and 8 for every whole month, it's also rounded down so if the accrural was 26.3345 days then the days earned would be 26

 

[melindaladd]

Thank you so much Simon! Perfect. Sorry the response was so slow!

You have no idea how greatful I am!!!!