Formula for PTO Accrual

[JTPhillips]

Hello,

I am working on a PTO accrual spreadsheet. Although I have been teaching myself everything from sites and books, I'm having difficulty constructing this last formula.

In this particular cell, I am calculating total pto for the year. In this case, the employee was hired 11/14/11, and accrues 1 day monthly from 1/1/12 to 11/14/12, so DATEDIF(1.1.12,11.14.12,"m")*accrual rate of 1 day per month.

On 11/14/12, she begins to accrue 1.25 days a month until 1/1/13. I just can't figure out how to take this number, and then add it to what accrues at the different rate from 1/1/12-11/14/12, or how to construct a formula that encompasses both date ranges and accrual rates. Perhaps I should also be using units in days vs. months? I don't see where to attach a file.

I try to teach myself as much as I can...but I'm lost.

If anyone has thoughts it would be greatly appreciated.


Thanks

 

[wigi]

If you want to have helpful people look at your problem and give valuable feedback, please reply to earlier feedback you were given. http://www.mrexcel.com/forum/showthread.php?t=620958

If not, why would people spend effort on your problems?

Thank you to consider this. I don't want to be nasty but politeness would teach us to do this.

 

[Dave Patton]

Excel Workbook
	A	B	C	D	E
1	Start
2	Nov 14, 2011		Months	Rate	Total
3	Jan 01, 2012	Nov 14, 2012	10	1.00	10
4	Nov 14, 2012	Jan 01, 2013	1	1.25	1.25
5
6	Nov 14, 2012	Jan 14, 2013	2	1.25	2.5
2a

Excel 2003

Cell Formulas
Range	Formula
C3	=DATEDIF(A3,B3,"M")
C4	=DATEDIF(A4,B4,"M")
C6	=DATEDIF(A6,B6,"M")
E3	=C3*D3
E4	=C4*D4
E6	=C6*D6

What is PTO?
What are the rules/criteria?
Ensure that you account for the total number of months.

 

[JTPhillips]

Hi,

Thank you so much. I appreciate your help! PTO stands for "Paid Time Off".
Will try to incorporate your suggestions into my existing accrual rate table.

I guess there is no way to write the formula to have both calculate and add?
I'm not using dates in the formula, but named cells)

=DATEDIF(year.begin,anniv.date,"m")*1.00, =DATEDIF(anniv.date,year.end)*1.25

Is there a way to combine these formulas? I've been working on nested formulas but struggle a bit!

 

[Dave Patton]

"I guess there is no way to write the formula to have both calculate and add?
I'm not using dates in the formula, but named cells)"

It may be possible.
What are the rules/criteria?

Please provide information and an example with results.
When does the accrual start?
What are the rates and when do they begin?

 

[JTPhillips]

Hello and Good Morning,

Thank you in advance for your help. Hopefully I've detailed enough below.


Hire Date: 10/25/11 Year End: 1/1/13
Todays Date: 4/11/12 (Today())
Years Of Service: 0 (Formula)
New Year Date: 1/1/12
Anniversary Date: 10/25/12
Current Accrual: 1 (Lookup table below)

Policy
Years of Service Days Accrued Annually Days Accrued Monthly
0 12 1.000
1-5 15 1.25
6-9 18 1.50
10 21 1.75


I have been attempting to write a formula that shows total vacation accrual for 2012. In the above case, the employee accrues 1 day per month from 1/1/12-10/24/12. They then accrue 1.25 days per month from 10/25/12-1/1/13.

Currently, I have been playing with =DATEDIF(new year.date,year.end,"m")*accrual rate (1.00), as one part, and then =DATEDIF(anniv.date,year.end,"m")*accrual rate (1.25). This somewhat works
when I do them separately, and then add them together to get accrued days from one part of the year and then the other.

Couple of issues...Since the unit is months, I don't think the formula is capturing the credited days correctly, and if the formula can be combined to capture both date ranges and accrual rates in one formula.

I'm self taught, and pour over books and sites to continue to learn. Having difficulty with nested formulas.

Thanks!

 

[Dave Patton]

Excel Workbook
	A	B	C	D	E	F	G	H
1								PTO accrual
2	Oct 25, 2011	Cumulative Months	Total Accrual	Accrual by Year		Months	Rate	Rate Differential
3	Dec 31, 2011	2	2.00	2.00		0	1.00	1.00
4	Apr 11, 2012	5	5.00			12	1.25	0.25
5	Dec 31, 2012	14	14.50	12.50		60	1.50	0.25
6	Dec 31, 2013	26	29.50	15.00		108	1.75	0.25
7	Oct 26, 2021	120	165.00
2a

Excel 2003

Cell Formulas
Range	Formula
B3	=DATEDIF($A$2,A3,"M")
C3	=SUMPRODUCT(--(B3>$F$3:$F$6),B3-$F$3:$F$6,$H$3:$H$6)
H3	=G3-N(G2)

 

[MikeWx]

The array formula in Cell B3 calculates the entire year's PTO accrual. Array formulas need CTL SHIFT ENTER instead of just ENTER. For the example that you gave, it calculates nine months of accrual at 1 day per month, then 3 months accrual at 1.25 days per month. Month end 10/31 through 12/31 are past the employee's anniversary date, so they are calculated at the rate for over one year's service. The table in D2:E5 determines the accrual rate from the year's of service.

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px"><COL style="WIDTH: 75px"><COL style="WIDTH: 93px"><COL style="WIDTH: 75px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: right">1/1/2012</TD><TD style="TEXT-ALIGN: right">12/31/2012</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>HIRE DATE</TD><TD>PTO ACCRUAL</TD><TD> </TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">10/25/2011</TD><TD style="TEXT-ALIGN: right">12.75</TD><TD> </TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1.25</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right">1.5</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: right">1.75</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B3</TD><TD>{=SUM(--(EOMONTH(A1,ROW(INDIRECT("1:12"))-1)<(TEXT(A3,"MM/DD/")&YEAR(A1))*1))*IFERROR(VLOOKUP(YEAR($A$1)-YEAR(A3)-1,$D$2:$E$5,2,1),0)+SUM(--((TEXT(A3,"MM/DD/")&YEAR(A1))*1<EOMONTH(A1,ROW(INDIRECT("1:12"))-1)))*VLOOKUP(YEAR($A$1)-YEAR(A3),$D$2:$E$5,2,1)}</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


Is this closer to what you are looking for?

Mike

 

[JTPhillips]

Hi Mike,

I just picked this back up this morning. I'm going to give it a try and let you know! Thank you so much for your help.

 

[kentucky000]

Oh my goodness I thought I did. Sorry