Accruing vacation and tracking vacation (Excel 2003)

Topic

New Reply

Good evening…I am attaching a sample of a spreadsheet that I am trying to finish off….I think that most of it is self-explanatory. What I am having trouble with is real-time vacation accrual: vacation accrues at the rate of 2 days/month, at the end of each month. In my sample, “A” has no vacation left over from last year (ie: vacation that he earned in 2006); he earned 24 days in 2007 that he can use in 2008…as 2008 unfolds, he will earn 2 days at end of each month..(ie: at end of January he will be holding 26 vacation days) I want an accurate count of how much vacation he has available to him at any point in the year and so I am looking for a formula in D13:M13 that will ‘see’ month-end (as it occurs..maybe a TODAY() formula?) and add 2 days at the end of each month to the cells below in A14:M14 (so that it is a ‘real-time’ picture) which will then total up in row 15…..

Reply | Quote

Replies

(Edited by sdckapr on 15-Dec-07 11:31. Corrected mistake in formula)

How about this?

=IF(YEAR(Today())>=2008,DATEDIF(DATE(2008,1,1),Today()+1,”m”),0)*2

If the date is 2008 or later, there will be zero days. otherwise on the last day of each month 2 more days will be added. If the 2 days are added at the start of the following month you would use:

=IF(YEAR(Today())>=2008,DATEDIF(DATE(2008,1,1),Today(),”m”),0)*2

Steve

Reply | Quote

???..???…Steve-I tried this formula in the sample, in D13, and it says #NAME…..is this a formula that will not work until 2008 actually arrives? I think there is something missing b/c often when I enter a formula, I do so in small case letters (and then if the formula works, everything converts to upper case, and if it doesn’t convert, then I know something is missing….in this case, the ‘today’ didn’t convert)….and originally, it was saying that there were too many functions….

Reply | Quote

Sorry, I was testing it with a range named “Today” since “Today” does not change. “Today” in the formula should be “Today()”. I will correct my original…

Steve

Reply | Quote

Thank you for that..it works beautifully!

Reply | Quote

Hi Steve….I jumped the gun a bit on this with my last post….I forgot (didn’t notice until I started using it) that, if possible, the formula should recognize that the 2 days only commences upon employment…..for example, if someone were to be hired in Sept, they would only earn 8 days for that year (2 * 4 months)….the existing formula overlooks the ‘month of hire’……I re-did my sample spreadsheet and you will see that D and J (who started work in 2007) show the same amount of accrual as everyone else, when in fact, at this point, D should only have 20 days (Feb-Nov) and J should only have 10 (July, Aug, Sept, Oct, Nov….b/c he started after mid-month and so earned nothing for June…only July onward……I am re-attaching the workbook, and changed it to be for 2007 so that the formulae actually work, b/c we are in 2007……I realize now that this may becoming too complex (?) but is there any way to tie all of this into the ‘date of hire’, such that holidays are only earned starting with commencement (and only if hired before the 15th of the month; if hired after, then accrual begins as of the 1st day of the month after being hired)…?

Reply | Quote

How about In D13:

=IF(YEAR(TODAY())>=2007,DATEDIF(MAX(D10,DATE(2007,1,1)),TODAY(),”m”),0)*2

Steve

Reply | Quote

Thank you…it seems to work just fine….you guys on this forum (you, Hans, Jerry and others are so helpful, we would be lost without you) Thank you; enjoy the rest of the weekend.

Reply | Quote

Steve….can you explain OFFSET to me….the Help files in Excel make no sense to me…I am attaching a 2nd version of the vacation tracker that you helped me with….I’ve added a 2nd page (Report) and I am trying to use OFFSET to copy the numbers…I can get the Reference part right, but I can’t get the formula to move from column to column (to the right) in order to pick up the numbers for each person……thank you, again

Reply | Quote

If you want to “hardcode” the offset values, you must change the 2 from each one (or calculate it). You could calculate it from the row/column you are in. In D5 enter:
=OFFSET(Vacation!$D$11,COLUMN()-4,ROW()-5)

Copy D5 to D5:I9

But you will have to change the formulas based on where you place the data in the sheet. I think basing it on the row/column “headings” is more general. In D5

=OFFSET(Vacation!$D$11,MATCH(D$4,Vacation!$A$11:$A$16,0)-1,MATCH($C5,Vacation!$D$9:$H$9,0)-1)

Or to eliminate the “-1″s just start at C10:
=OFFSET(Vacation!$C$10,MATCH(D$4,Vacation!$A$11:$A$16,0),MATCH($C5,Vacation!$D$9:$H$9,0))

I prefer instead of OFFSET I would use an INDEX with 2 MATCHes I think this is more “intuitive”. In D5 enter:
=INDEX(Vacation!$D$11:$H$16,MATCH(D$4,Vacation!$A$11:$A$16,0),MATCH($C5,Vacation!$D$9:$H$9,0))

Alternately with either INDEX or OFFSET you could point to the “indexvalue” of the “offset” of the rows and columns as (possibly hidden) row and column instead of calculating it from the MATCH.

Steve

Reply | Quote

…thank you for all of that, Steve…it will take a bit to digest it all, but it sure gives me some different ways to do it….thank you!

Reply | Quote

Hello again Steve…I have returned to this post (from 2 weeks ago)….you had helped me with a formula that would automatically show vacation accrual as the months passed during a year; as I read your formula (which is =IF(YEAR(TODAY())>=2007,DATEDIF(MAX(D10,DATE(2007,1,1)),TODAY(),”m”),0)*2), it is set up so that if someone starts work anytime up to and including the 15th day of a month, they earn 2 days vacation (and if they don’t start to work till after the 15th they earn nothing in that month.

Is it possible to adjust it so that if someone starts working anytime from the 16th on, they would earn 1 day (for that partial month) and thereafter would earn 2 days for each complete month worked….in other words, for each 1/2 month, they earn 1 day…?? Thank you…

Reply | Quote

How about this?

=IF(YEAR(TODAY())>=2007,2*DATEDIF(MAX(D10,DATE(2007,1,1)),TODAY(),”m”)+(DAY(D10)<16)+1,0)

Reply | Quote

Hi Hans…..your code seems to work fine with one exception…I have attached a sample wbook and you’ll see that everything seems to calculate properly for some (B,C,D etc) but not for all (see A)…??..??….what I am shooting for is that if you start work anytime up to & including the 15th of a month, it counts as a full month for vacation accrual purposes; if you start anytime from the 16th on, that is a 1/2 month for vacation accrual purposes…..and, conversely, if you quit up to and including the 15, it counts as a 1/2 month for vacation accrual purposes..and if you quit anytime from the 16th-month end, it counts as a full month for vacation accrual purposes…I have tried to explain this in the attachment

Reply | Quote

It’s 2008 now, so the formula includes January of this year. Try this version:

=IF(YEAR(TODAY())>=2007,2*DATEDIF(MAX(D11,DATE(2007,1,1)),MIN(TODAY(),DATE(2007,12,31)),”m”)+(DAY(D11)<16)+1,0)

The formula doesn't take a quit date into account – that's a new element you're introducing now, and I don't see that information anywhere in the sheet.

Reply | Quote

Thank you Hans…perhaps I made things confusing by starting a calendar in 2007…I was using that sample so that I could see if my formulae would work (b/c if I started in 2008, nothing would work b/c it’s too early in the year)….I have added a “quit date” (called Accrual period ends and this will be Dec 31, 2008 unless the person quits before that date)…..in actual fact, I will start this whole thing as of Jan 1, 2008 (but I use some of 2007 to test the accrual)….would you mind taking a look at my new sample showing the quit date and see if that can be figured into the formula?

And, b/c I am not going to be using any dates from 2007 in my actual wbook, can I confine it to 2008 simply by changing 2007 (in your formula) to 2008 & leaving all else the same?

Reply | Quote

Do you want the workbook to show vacation accrued within one year only, or over a longer period of time?

Reply | Quote

Hans…i would do a year at a time….eg: Jan 2008-Dec 2008….the vacation accrues at the END of a month, so in Jan 2008, I will take the left-over 2007 vacation and move it to the carried/fwd row in my 2008 workbook… on Jan 16, it should add 1 day for everyone whose “accrual period start” was b/w Jan 1/08 & Jan 15/08…..then on Feb 1, it should add another day for anyone whose ‘accrual period start’ date was b/w Jan 1/08 and Jan 15/08 and 1 day for those who started on or after Jan 16…so that some will accrue 2 days for Jan and others (people who started on or after Jan 16) will accrue 1 day for Jan……does that clarify things?

Reply | Quote

How about this:

=IF(YEAR(MIN(TODAY(),D12))16),1)),MIN(TODAY(),D12),”m”)+(DAY(D11)>16)*(YEAR(D11)>=YEAR(TODAY())))

The end date is the minimum of D12 (end date) and today. The start date is max of the first day of the current year and either the first day of the startmonth (if the startday 15). It checks the number of months and multiplies by 2 and adds a day if the startday is >16 and also in the current year.

Note: it will all be zero for this year since no one has accrued any vacation. You can test it in a previous year if you change your computer date to last year. or change TODAY() to cell that you can vary….

Steve

Reply | Quote

Edited by HansV to add a clarification

Steve, I think you should add 2 to the result of the “else” part of your formula. For a whole year, the accrued vacation should be 12*2 = 24 days, but your formula yields 22.

Reply | Quote

That depends on how you look at it. You can only earn 22 days within that year (which is what the formula calculates). You start a year with at least 2 days of carryover from the last month. The formula presumes that the 2 days are added AFTER the month ends. from Jan 1 – Jan 31 you have accrued none. You accrue the 2 days on Feb 1 after you work for the month. Thus on Dec 1 you have the 11 months (22 days) which are the only ones available even on Dec 31. You would not get December’s until Jan 1 and then it should be accounted for in “carryover” in some form from last year.

If you started work from 12/1 to 12/15 you would have no vacation that year, but would start the next year with at 2 days carryover in Jan (the formula for the earned number is zero). In a year you earn 2 days each worked month. Thus from on Feb1 – Dec1 (11 months= 22 days) and then you earn 2 days (carryover) on Jan 1 from the previous year when you worked through December.

It all depends on when one is calculating the “accrual date”. One could argue at the 16th you should earn 1 day, and then get the 2nd day at the end, but that did not seem to the setup. I understood it to be after the month was worked, the vacation was earned.

If one wants to give the vacation on the last day of the month, the MIN can be changed to use Today+1, instead of Today():
=IF(YEAR(MIN(TODAY(),D12))16),1)),MIN(TODAY()+1,D12),”m”)+(DAY(D11)>16)*(YEAR(D11)>=YEAR(TODAY())))

And this would give 24 on 12/31.

Or one could include the explicit “carryover” from December as 2 days earned during January and not view it as “carryover”. Then you could have 24 days earned in a year. This would require the “start” to be December 1st of last year not Jan1 of this year, but this gets complicated, since moe conditions must be added to adding the extra day of the start is after 12/15 and the start day might have to be 12/1of the previous year.

But perhaps I am looking at it wrong…

Steve

Reply | Quote

Well, the OP can decide which approach he wants…

Reply | Quote

Sorry to be so dense guys, but now I am somewhat confused by your difference in approaches…..if I use a 2007 calendar (so that I can ‘test’ it) my calendar would run from an Accrual Start Period of 1/1/07 until 12/31/07…I have attached sample3.xls to illustrate…I have 6 employees(A-F)…the year of hire (row 10) is for info only. Row 11 (Accrual periods starts) is Jan 1, except for E & F, who were hired on May 1, 2007 and Sept 16, 2007 respectively. Row 12 (accrual periods ends) will be Dec 31/07, except for B & C (who quit on Nov 5/07 and Sept 23/07 respectively).

I need a formula for row 14 that will give an employee either:
(i) 2 days vacation for each month worked, so long as he has worked for more than 1/2 of that month (ie: started b/w the 1st – 15th & worked till month-end, or, was working on the 1st and stayed until at least the 16th) [employees A, C, D, E] and,

(ii) 1 day vacation if he has worked for less than 1/2 of the month (eg: started on the 1st, 2nd, 3rd etc and quit no later than the 15th [employee B], or, started on the 16th, 17th, 20th etc and worked till month-end) [employee F]

I apologize for not having been this clear earlier, but I didn’t see that it would be so complex until I saw your answers….I assume that even though the accrual period ends no later than 12/31/07, if I were to look at the numbers in row 14 on Jan 1, 2008, they would reflect any vacation earned in Dec 2007 (and then I would use those figures as the carry-fwd numbers for row 13 in my 2008 calendar (or will the formula stop working as of Jan 1, 2008???, and if so, do I include 1/1/08 as the last date as I did in sample 3.xls??).

Reply | Quote

Before I look: is there a difference between the attachments to your two most recent replies? If not, there wouldn’t have been a need to post two replies with the same attachment.

Reply | Quote

No…the attachment is the same for both, but I wasn’t sure if I replied to you, whether Steve would also get notice of my reply, so I copied it to him (and sent the same attachment)….sorry if that was overkill..

Reply | Quote

It isn’t necessary to post the same reply (and attachment) twice; if you wish you can mention in your reply that it is directed at A, B and C.

I’ll have a look.

Reply | Quote

OK…thank you !.

Reply | Quote

In the attached version, I have unmerged cell D1 and split the contents over D1, D2 and D3 (these cells are centered over columns D:G for display reasons).
Cell D1 now contains the relevant year. I’ve used this in a slightly modified formula so that you can keep the formula =TODAY() in cell D6.

Reply | Quote

Hmmm…..it seems to work fine, and yet if I change to vacation entitlement (eg: 3 days per month and 1/5 days per “half” month, the numbers don’t work out…I did this b/c I assume that the formula will or should work no matter what the enititlement (ie: that it is not specific to a vacation accrual of 2 days/month)….I am attaching your sample, and I have modified it to contemplate vacation accrual at 3 days per month and 1/5 days per “half” month…you will see that it doesn’t add correctly for employees A (should be 36 days), C (should be 27 days), D (should be 36 like A) and E (should be 24)…..the error only seems to appear where a start date is Jan 1 and/or an end date is Dec 31……see what I mean?

Reply | Quote

It’s hard to hit a moving target. You’re changing the requirements each time!

You modified the formulas incorrectly. To make it easier, I have stored the days per whole month and per half month in cells I1 and I2 in the attached version, so that you don’t have to adjust the formulas each time.

Reply | Quote

THANK YOU so much for your patient explanation and work…one more question if you don’t mind….there may be occasions where the numbers are fractions such as, if vacation entitlement was 2.5 days/month and 1.25 per/half…I like the idea of putting that info in cells I1 and I2…however, if a result were to be, for example, 6.25 days or 5.75 days, I would round that up to the nearest 1/2 (ie: 6.25 would become 6.5 and 5.75 would become 6 etc)….what rounding formula can I insert at the front of the formulae in row 14 to round the number up to the next nearest 1/2 ?

Reply | Quote

Are you going to want to round to other fractions in the future, perhaps?

Reply | Quote

I would just want to round to halves or wholes (eg: round up to ?.5 or ?.0)…I was working with =CEILING(Your formula),.05) thinking that will take a 3.25 up to 3.5 or a 8.75 up to a 9 (b/c nobody takes a 1/4 day vacation)

Reply | Quote

You can use CEILING indeed. I’d put the “unit” 0.5 in cell I3, so that it can easily be changed, like the days per month and per half month. You can then use the formula

=CEILING(IF(YEAR(MIN($D$6,D12))>=2007,$I$1*(MONTH(MIN(D12,DATE($D$1,12,31)))-MONTH(MAX(D11,DATE($D$1,1,1))))+$I$2*IF(YEAR(D11)=$D$1,DAY(MAX(D11,DATE($D$1,1,1)))15,1),0),$I$3)

Reply | Quote

(Edited by dmcnab on 05-Jan-08 20:40. ..more explanation and new sample wbook)

…??!!???@@??!!…grrr….I do not understand what is happening here….I took the formula you gave me in the sample..it works perfectly in my 2007 sample…the moment that I insert it into my 2008 workbook (and make the appropriate changes) it shows the full amount accrued (3 days/month = 36 days) immediately when it should only be showing the accrued amounts AS THEY ACCRUE AS THE YEAR UNFOLDS (eg 3 days at end of Jan, 6 days at end of Feb, 9 days at end of March)…….I had it working like that before but now it’s not…???….any ideas?

It seems that it is accruing the vacation prematurely…if I change your formula to IF(YEAR(MIN($E$7,D13))>=2008,$D$2*(MONTH(MIN(E7,DATE($E$2,12,31)))-MONTH(MAX(D12,DATE($E$2,1,1))))+$D$4*IF(YEAR(D12)=$E$2,DAY
(MAX(D12,DATE($E$2,1,1)))15,1),0) it gets is closer (shows accrual to be 2.5 days) but it should NOT show anything til Jan 16 at the earliest, and at that point it should ONLY show 1.25 for anyone who was working b/w Jan 1-15; and on Feb 1, it would add 1.25 days vacation for anyone who started b/w Jan 1-15 and add 1.25 for anyone who started b/w Jan 16-31……???…I am attaching my current 2008 sample so you can see what I am talking about….

Reply | Quote

Because of the continuously changing requirements, the formula had become too complicated for me to keep track of how exactly it worked.
I have started from scratch, and inserted a large number of rows with intermediate results. These rows can be hidden.
See attachment.

Reply | Quote

This comes to some of the questions from post 686,501…

What days do vacation accrue on? Is it continuous or incremental. Do you accrue partial days on a daily basis, do they accrue at half-months (15th or 16th) or at the end of a month (last day of month) or at the beginning of next month?

Consider an accrual for 2007 with several employees that is now 2008. Lets assume that no one has taken any vacation in 2007 or 2008.

How many days do they have if the date is Dec 30, 2007? on Dec 31, 2007?
How many days are carried from 2007 and how many do they have in 2008 if the date is Jan 3, 2008?
How many days are carried from 2007 and how many does he have in 2008 if the date is Jan 18, 2008?
How many days are carried from 2007 and how many does he have in 2008 if the date is Feb 2, 2008?

A Employee started work on Jan 1, 2007 and quit Dec 31, 2007
B. Employee started work on Jan 1, 2007 and is still employeed.

C. Employee started on Nov 30, 2007 and quit Dec 31, 2007
D. Employee started work on Nov 30,2007 and is still employeed.

E. Employee started on Dec 20, 2007 and quit Dec 31, 2007
F. Employee started work on Dec 20, 2007 and is still employeed.

G. Employee started work on Jan 1, 2008 and is still employeed.

I think if you answer for these situations, we may be able to get a better handle on what your target is. My calculations have presumed that the days come after the last day of the month. (I changed from 2 days per month to 3 days per month):

			Dec 30, 2007	Dec 31, 2007		Jan 1, 2008			Jan 18, 2008			Feb 2, 2008
	Start	End	2007 earned	2007 earned		2007 carryover	2008 earned		2007 carryover	2008 earned		2007 carryover	2008 earned
A	Jan 1, 2007	Dec 31, 2007	33	33		36	0		36	0		36	3
B	Jan 1, 2007		33	33		36	0		36	0		36	0
C	Nov 30, 2007	Dec 31, 2007	0	0		3	0		0	0		0	0
D	Nov 30, 2007		0	0		3	0		0	0		0	3
E	Dec 20, 2007	Dec 31, 2007	0	0		1.5	0		1.5	0		1.5	0
F	Dec 20, 2007		0	0		1.5	0		1.5	0		1.5	3
G	Jan 1, 2008		0	0		0	0		0	0		0	3

But those numbers will change dependent on when the days are accrued: Last day of month, 1st day of next month, half accrual at middle of month, etc…
Steve

Reply | Quote

Hello (again) Steve and Hans…..I really have to apologize for the apparently semi-sloppy way in which I have presented all of this….it just seemed to spin out of control and became much more complex than it needed to….I think that the easiest way is to, somewhat, “begin at the beginning”….I felt that I was almost there a week ago, so if I can back up a bit…

When someone is hired they earn (accrue) VAC at the rate of 2.5days/month, so long as they start no later than the 15th of the month. If they start b/w the 16th day and the end of the month, they earn 1.25 days for that first month, and then 2.5 days for each full month thereafter. VAC earned in one month is available to them as of the 1st day of the following month. If they quit anytime b/w the 1st-15th, they earn (accrue) 1.25 days; if they quit b/w the 16th and the end of the month, they have 2.5 days VAC owed to them. All VAC that is earned is available to the employee as of the 1st day of the month following the month in which it
was earned. The VAC accrual period normally runs from Jan 1 – Dec 31. Exceptions would be for someone who is hired, or quits, partway through a year….their accrual period would start at the time of hire and it would end when they quit. Any VAC that is earned but not taken is carried forward to the next year. If employee “A” is on staff as of Jan 1, then as of Feb 1, he has 2.5 days VAC accrued; as of March 1, he has 5 days VAC accrued, and so on and so on.

I am attaching a doc called vacation scheduler old.xls…..I have added some comments to it…it is based on a formula that Steve suggested and I had to make a change (just so that I could test it for 2008)….I think that if you look at it, along with the comments, I will finally have clearly explained where I am trying to go with this…as I said, it somehow became much more complex than I suspect it needs to be and by backing up a bit, I believe that I have simplified it and made the objective much easier to understand…thank you so much for your help…..

Reply | Quote

(Edited by sdckapr on 06-Jan-08 11:16. Added PS)

Could you answer my questions Employees A-G in post 686,583? This would go a long way (at least for me) of understanding your system and what you are after….

Your descriptions still seems to suggest to me that the formula I gave in post 686,493 is what you want (chanign the 2 to 2.5 and using 1.25 for the “half-month” at the end.:
=IF(YEAR(MIN(TODAY(),D12))16),1)),MIN(TODAY(),D12),”m”)+1.25*(DAY(D11)>16)*(YEAR(D11)>=YEAR(TODAY())))

The 1.25 or 2.5 days (depending on start date) from Dec of the previous year would be included in a calculation of carryover, since it is never accrued since it is earned in a year that is past when it is received…

But as I said earlier, perhaps I am looking at it incorrectly (which is why I asked about the A-G examples….)
Steve

Another situation, ENDDATE:
If someone leaves before the end of the month how much do they accrue? I have presumed that they get NO vacation unless they finish the month (they only get a “half-month” based on their start date). If they are allowed “partial months” for accrual how is that calculated?

Reply | Quote

Hmmm..OK…I will make the following assumptions:

1. if your start date is 16th day of the month, you earn 1.5 days VAC for that first month
3. each complete month thereafter, you earn 3 days VAC
4. VAC accrues to you on the first day of the “next’ month (ie: if you start in Feb, the VAC you earn in Feb accrues to you on March 1; the VAC you earn b/w Mar 1-31 accrues to you on Apr 1 etc etc)

If you worked for Sept 1-30, you would earn 3 days…if your last day was Sept 30, on Oct 1 you would be ‘owed’ 3 days VAC….in actual fact, either you would get paid out for those 3 days, or not come to work for the last 3 days of Sept (and be paid anyway)….how the person is compensated for the days is irrelevant…it is only important to calculate the number of days.

In your questions about A-G, based on my assumptions (3 days etc), the following would occur:

A: works from Jan 1/07-Dec 31/07—would earn 36 days VAC during those 12 mths…he might have used some of them thru the year, but what he took + what he didn’t take = 36
B: works from Jan 1/07 to present–would earn 36 days VAC druing those 12 months; anything not taken as VAC during 2007 would be carried fwd to 2008
C: works from Nov 30/07-Dec 31/07–would earn 1.5 for Nov 30 & 3 days for Dec; anything taken in Dec is deducted and he is paid out for balance
D: works from Nov 30/07 – present– earns 1.5 for Nov 30 and 3 days for each month thereafter; anything not taken as VAC during 2007 would be carried fwd to 2008
E: works from Dec 20-31–earns 1.5 days
F: works from Dec 20/07 to present–earns 1.5 days for Dec and 3 days for each month thereafter
G: works from Jan 1/08–earns 1.5 days if they quit no later than Jan 15; earns 3 days if they stay beyond Jan 15…..this example sounds odd and we wouldn’t have a situation where someone quits within the first month, but I know you wanted on answer…

Does all of this help? Thank you

Reply | Quote

I am looking for the details for the calculations. Could you please fill in the table, there are subtle differences in some of them and what the formulas will be calculating.

If the person gets 36 days on 12/31 you are indicating that the vacation comes on the last day of the month not the first day of the next month. (exceptions complicate the formula and I suggest you stick with one rule).

If the person may take vacation for the last 3 days of the month this suggests that the vacation is earned 3 days before the month ends, not the 1st day of the next month…

Steve

Reply | Quote

Regarding the ENDDATE:…I think I answered this but if not…..if you quit anytime up to and including the 15th of the month, you earn 1.5 VAC for that mth; if you quit b/w the 16th and the 30th/31st, you earn 3 days VAC…..and that should be calculable based on the date of the “Accrual period ends”….

Reply | Quote

(Edited by sdckapr on 06-Jan-08 15:18. Added PS)

Some extreme examples to confirm the logic you proposed:

If someone starts on Jan 14 and quits on Feb 5 by your descriptions he earns 3 days for Jan and 1.5 for Feb even though he has worked for less than 1 month. By other logic one could look at total time and say 3 weeks and give only 1.5 days earned. That is big discrepancy: 1.5 vs 4.5 days.

If someone starts on Jan 30 and quits on Feb 1 by your descriptions he earns 1.5 days for Jan and 1.5 for Feb even though he has “worked” for only 3 days. He essentially can start and quit, get paid for 3 days and never show up to work

I am not trying to be “contrary”, I am just trying to understand the logic to make a formula. I need to understand all the “exceptions” and variations to be able to account for them.

Steve

PS working with weeks (or days) instead of months would be so much easier. Months vary in weeks and days. DATEDIF works on “integer months”, working directly with the numbers can give fractions of weeks or “months” so rounding is so much easier. With Datedif one must decide on the start and enddate and account (as an exception) for the fractional month.

Reply | Quote

Very funny, Steve…….you hit the nail right on the head when you talk about extreme examples…..and that is what I am trying to avoid (ie: setting up formulae that, once being used, beget more problems than they solve….after the last posting, I was thinking about the big picture a little more and concluded that if I dice things up too finely, then I am set up for constant reviewing of how much VAC people have and the last thing I want is to get to a point where staff are quibbling over 1/4 days etc etc…or taking an extreme position similar to one of the examples that you gave……b/c the issue of partial days of VAC will ONLY arise out of the first (or last) month of employment, I have concluded that I need a formula that awards 2.5 days per whole month worked (and the VAC accrues/crystallizes on the 1st day of the next month…and, so long as they start before the 15th, they get 2.5 for that first month; if they start after the 15th, they get nothing for the first month. When they quit, that can be handled manually b/c most people will work to a month-end and the few that don’t I will figure out manually.

As I said, at this point, I am equally interested in a formula that, essentially, works in one fashion for Jan-Nov (ie: shows the VAC as earned on the first day of the next month, and, shows VAC earned in Dec in the year-end total (only b/c it is easier to know how much to carry fwd to the next year)…if that is too complicated, then Dec’s VAC can be added manually to the total when those numbers are inputted into the 2009 scheduler…..I really hope this makes it easier to follow…I know that you wanted answers to some questions & details, but I fear (as you have illustrated) that by going the whole way with this, it will be more problematic than it currently is…..thanks again for your interest and time and I look forward to seeing what you suggest. Thanks !

Reply | Quote

[q]and that is what I am trying to avoid (ie: setting up formulae that, once being used, beget more problems than they solve[/q]

Well, unless you set up formulas with “the precision required for a rocket launch,” that is exactly what you are going to get which this thread seems to prove.

Reply | Quote

….you’re right Legare….can I avoid problems with a formula that:

(a) accrues the 2.5 per month;
( “credits’ it to the person on the first day of the following month;
© if possible, somehow permit the Dec credit to form part of the year end total (so that someone who works 12 months will see 30 days credits to them as of Dec 31 (which is carry fwd to the next year and the process starts anew)
(d) and if the 1st or last month of work are partial months (determined by the dates of Accrual period Starts and Accrual periods Ends), they get a credit of .57 days for each 5 workdays of that partial month (which could be rounded up to the nearest 1/2 day)

This sort of formula would do precisely what I need, and I suspect that the ‘hard’ parts to compose would be for paragraphs © and (d)…..???

Reply | Quote

This essentially is what I posted earlier. I added the exception of using Dec 31 as the “end date” (instead of Jan 1) and added an extra 1.25 days if the enddate is >16 and is in the current year.

=IF(YEAR(MIN(TODAY(),D12))16),1)),MIN(TODAY(),D12)+(MIN(TODAY(),D12)=DATE(YEAR(TODAY()),12,31)),”m”)+1.25*(DAY(D11)>16)*(YEAR(D11)>=YEAR(TODAY())))+1.25*(DAY(D12)>16)*(YEAR(D12)>=YEAR(TODAY()))

Steve

Reply | Quote

Whether the extreme examples would actually occur in real-life or not, they will calculate.

Is it fair that someone who works from Jan 15 to Dec 16th (essentially working 11 months) gets vacation the same as someone who worked the full 12 months?

Or that someone who works from Jan 16 to Dec 15 gets 2.5 days less vacation than “that someone” just because he worked 2 days less? Can this person claim he was on vacation the Jan 15th and on Dec 16th after the fact) and claim an additional 0.5 days in “vacation pay” after quitting?

Personally, I am not interested in the “parity”, I am just trying to understand the “rules” so I can calculate them…

Steve

Reply | Quote

Hi Steve…..I agree wtih you re: the parity, which (I hope) explains my comments in my post 686,649 and my follow up remarks to Legare……can I assume that you wrote this most recent formula based on the attached sample that was with with my post 686,589? If so, is there any way for me to test is, or should I just wait until later in January (as time passes) and watch to see how it calculates?…the formula doesn’t make reference to the cell that contains the TODAY() formula at the top (where it says “Vacation scheduler as at: “Today())……

Reply | Quote

You can change the “Today()” in the formula to reference a cell instead to test it (if the cell will contain =Today() in the working copy, you don’t even have to change the formulas). I was testing (as mentioned earlier, by defining a named range as “Today” and editing the formula to test then re-editing it.

Steve

Reply | Quote

Steve…I am sorry if I am testing the limits of your patience….unformtunately, I don’t understand the formula that you gave me and that I included in the attachment that was with my last post to you on Jan-06-08 @ 18:54 post 686,681

The formula contains references to cells in row 11 (Date hired) which has nothing to do with VAC accrual periods starting or ending etc…as well, it produces no results for the first 3 employees but does for the last one..??..?? Would you mind taking a look at that attachment and the formula to see where I may have mis-copied something? Please and thank you .

Reply | Quote

(Edited by sdckapr on 07-Jan-08 10:57. Added PS)

How about this in D15:

=IF(MAX(DATE(YEAR($E$7),1,1),DATE(YEAR(D12),MONTH(D12)+(DAY(D12)>16),1))>MIN($E$7,D13)+(MIN($E$7,D13)=DATE(YEAR($E$7),12,31)),0,$D$2*DATEDIF(MAX(DATE(YEAR($E$7),1,1),DATE(YEAR(D12),MONTH(D12)+(DAY(D12)>16),1)),MIN($E$7,D13)+(MIN($E$7,D13)=DATE(YEAR($E$7),12,31)),”m”)+$D$3*(DAY(D12)>16)*(YEAR(D12)>=YEAR($E$7)))+$D$3*(DAY(D13)>16)*(D13=YEAR($E$7))

Test it out with various start and end accrual dates and different values in E7 (the “Today cell”). It also reads from D2 and D3 to get the vac/month and vacation days in a half month.

If there are discrepancies or other exceptions, let me know:
accrual start
accrual end
Date in E7
What it calculates
What it should be and why it should be that

and i can try to modify the formula more…

Steve

PS: You did not miscopy, you added a row to your spreadsheet (requireing a mod to ghe formula) and I edited the formula I originally had with the original references to an earlier sheet.

Reply | Quote

Hi Steve….thank you for your help with this….everything seems to work fine with one exception…ps: I added a “CEILING” component to your formula, so that 1/4 days (3.25 or 6.75) would round up to the nearest half….however, it doesn’t seem to calculate a full month….if I use a start date of 1/1/08 and an end date of 1/31/08…on Feb 1, it should show 2.5 days…it only shows 1.5 (the 1.25 rounded up)…almost as if it overlooks that the start (or end) date is part of what is being counted (ie: ignoring that the person worked on those date)….I tried adding +1 to your formula (trying to artificially nudge the number up..?) but that didn’t work, so I took it out…I am attaching a sample and you’ll see what I am tlaking about…

Reply | Quote

(Edited by sdckapr on 07-Jan-08 19:36. Edited mistake with 15/16…)

How about. I stepped back a little too far in simplifying the end date so it did not Go into the next month.

=CEILING(IF(MAX(DATE(YEAR($E$7),1,1),DATE(YEAR(D12),MONTH(D12)+(DAY(D12)>15),1))>(MIN($E$7,DATE(YEAR(D13),MONTH(D13)+(DAY(D13)>15),1))),0,$D$2*DATEDIF(MAX(DATE(YEAR($E$7),1,1),DATE(YEAR(D12),MONTH(D12)+(DAY(D12)>15),1)),MIN($E$7,DATE(YEAR(D13),MONTH(D13)+(DAY(D13)>15),1))+(MIN($E$7,D13)=DATE(YEAR($E$7),12,31)),”m”)+$D$3*(DAY(D12)>15)*(YEAR(D12)>=YEAR($E$7)))+$D$3*(DAY(D13)<16)*(D13=YEAR($E$7)),0.5)

Steve

Reply | Quote

Amazing….based on what I just tested, it works beautifully…..thank you again…I will play with in, using different scenarios and I will get back to you no matter what (although, I suspect that it will be to tell you that it is perfect)..!!

Reply | Quote

This thread has become *very* long. Since the problem finally appears to have been solved, I will lock it now.

If you need to come back to this subject, please start a new thread, referring to this one.

Reply | Quote

A comment related to an earlier discussion of “partial months”. In your scheme, if a person works 1 – 15 days within a month, he gets a half month worth of vacation. If that person works 16 – 31 days in the month he gets a full month worth of vacation. Thus if starts on 1/31 and quits on 2/1 he is entitled to 2 days of pay + 2.5 days of vacaction pay (4.5 days of pay for 2 days of work). Or looked at another way: he can take the vacation, not come to work at all and be entitled to 2 days of pay and 0.5 days of vacation pay without working at all!

Something to consider is to use different limits. Perhaps he must work at least 21 days to get a full month’s worth and at least 10 days to get a half -month. Something like:

=CEILING(IF(MAX(DATE(YEAR($E$7),1,1),DATE(YEAR(D12),MONTH(D12)+(DAY(D12)>10),1))>(MIN($E$7,DATE(YEAR(D13),MONTH(D13)+(DAY(D13)>20),1))+(MIN($E$7,D13)=DATE(YEAR($E$7),12,31))),0,$D$2*DATEDIF(MAX(DATE(YEAR($E$7),1,1),DATE(YEAR(D12),MONTH(D12)+(DAY(D12)>10),1)),MIN($E$7,DATE(YEAR(D13),MONTH(D13)+(DAY(D13)>20),1))+(MIN($E$7,D13)=DATE(YEAR($E$7),12,31)),”m”)+$D$3*(DAY(D12)>10)*(DAY(D12)=YEAR($E$7))+$D$3*(DAY(D13)>10)*(DAY(D13)<21)*(D13=YEAR($E$7))),0.5)

Steve

Reply | Quote

Hi Steve……I am attaching a portion of the 2008 VAC workbook and I have inserted your formula in D15:G15…everything comes up 0, I assume b/c it is too early in the month?

Is there some way for me to test it by changing the date in E7….or changing the formula to refer to E7 (as that date will change) so that I can measure how the VAC accrues for different people as the date in E7 changes?

Reply | Quote

Hi again Steve. (and Hans) ..one more thought….I don’t want to approach this with the precision needed to launch a rocket….if someone were given 1.5 days VAC credit so long as they worked 1/2 of a month (b/c the “half month” thing will ONLY happen at the start or at the end of employment) that would probably be sufficient for my purposes….I could live with that and would be just as happy if in situations where the Accrual period ended on Dec 31 (ie the person was working as of that date) the Dec VAC was also included in the total shown in row 17 of my sample that I attached earlier….this would simply make it easier to carry the correct number fwd to the following year

Reply | Quote

Hi Hans….I am contining to work with Steve and you on solving my Vacation Accrual problems……you sent me a suggested formula in a workbook attached to your post 686532 ( found at http://www.wopr.com/w3tfiles/5-686532-Sample3using3days.xls)

The formula you proposed calculates everything at the beginning of the year…..did you intend that, or did you intend to set it up so that the VAC accrual tabulates as the year unfolds (ie as the date in D6 goes to Feb 1/08, the VAC earned in Jan shows up in row 14)…can your formula be altered to work like that? I am attaching your sample (renamed) to show you what I mean. Thanks,

Reply | Quote

You can very easily post a link to a post: simply select the post number below the subject, including the square brackets around it: [Post: 686,577]. Press Ctrl+C to copy it, then when replying, press Ctrl+V to paste. The Lounge software will automatically convert it to a link when you post: post 686,577.

post 686,577 contains a later version, but it doesn’t do what you want – it calculates vacation per half month, and it accrues at the beginning of each half month, not at the beginning of next month. Feel free to adapt it to your needs.

Reply | Quote

Sorry to be so dense guys, but now I am somewhat confused by your difference in approaches…..if I use a 2007 calendar (so that I can ‘test’ it) my calendar would run from an Accrual Start Period of 1/1/07 until 12/31/07…I have attached sample3.xls to illustrate…I have 6 employees(A-F)…the year of hire (row 10) is for info only. Row 11 (Accrual periods starts) is Jan 1, except for E & F, who were hired on May 1, 2007 and Sept 16, 2007 respectively. Row 12 (accrual periods ends) will be Dec 31/07, except for B & C (who quit on Nov 5/07 and Sept 23/07 respectively).

I need a formula for row 14 that will give an employee either:
(i) 2 days vacation for each month worked, so long as he has worked for more than 1/2 of that month (ie: started b/w the 1st – 15th & worked till month-end, or, was working on the 1st and stayed until at least the 16th) [employees A, C, D, E] and,

(ii) 1 day vacation if he has worked for less than 1/2 of the month (eg: started on the 1st, 2nd, 3rd etc and quit no later than the 15th [employee B], or, started on the 16th, 17th, 20th etc and worked till month-end) [employee F]

I apologize for not having been this clear earlier, but I didn’t see that it would be so complex until I saw your answers….I assume that even though the accrual period ends no later than 12/31/07, if I were to look at the numbers in row 14 on Jan 1, 2008, they would reflect any vacation earned in Dec 2007 (and then I would use those figures as the carry-fwd numbers for row 13 in my 2008 calendar (or will the formula stop working as of Jan 1, 2008???, and if so, do I include 1/1/08 as the last date as I did in sample 3.xls??).

Reply | Quote

Edited by HansV to add small correction

Try this version:

=IF(YEAR(MIN($D$6,D12))>=2007,2*(MONTH(MIN(D12,DATE(2007,12,31)))-MONTH(MAX(D11,DATE(2007,1,1))))+IF(YEAR($D$6)=YEAR(D11),DAY(MAX(D11,DATE(2007,1,1)))15,1),0)

You must put a date in 2007 in cell D6 to test.

Reply | Quote

Hi Steve….I re-posted my earlier post with some new info that I hadn’t thought of..along with a modified workbook….would you be able to take another look at it? Thank you

Reply | Quote