Rounding issues

[edeneye]

Hi EXCEL smes,

I'm having a tough time finding a severance formula that works for calculating through to the final result required due to either rounding issues or formula incompatibilities.
Year frac or Datedif formulas do not appear to work.

I require the following - Multiply an employee's regular wages for a regular work week by the sum of: the number of completed years of employment; and the number of completed months of employment divided by 12 for a year that is not completed.

Based on my country's regs, the result also needs to fit the following parameters to additionally calculate =IF(A2<5,"0",IF(A2<26,(A2*B2),IF(A2>=26,(B2*26))))

Thanks so much for your insights!

 

[Joe4]

Welcome to the Board!

If can be difficult to help when a formula is provided without seeing your data (and how it is laid out).
Can you post of few example of your data along with your expected results?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[edeneye]

Years of Service.xlsx
	A	B	C	D	E	F	G	H	I
1	Name	Hire Date	Hourly Pay	Weekly Pay	TERM DATE	YOS			Sev to MAX (DOH>5yrs)
2	EE1	26/10/1987	$27.69	$1,107.60	02/01/2024	36.21	36 yrs, 2 mths	36.25	28797.60
3	EE2	16/11/1998	$27.69	$1,107.60	02/01/2024	25.15	25 yrs, 1 mths	25.17	27850.83
4	EE3	24/10/2011	$27.69	$1,107.60	02/01/2024	12.20	12 yrs, 2 mths	12.25	13512.72
5	EE4	23/09/2021	$24.92	$996.80	02/01/2024	2.28	2 yrs, 3 mths	2.33	0
Sheet1

Cell Formulas
Range	Formula
F2:F5	F2	=(E2-B2)/365
G2:G5	G2	=DATEDIF(B2,E2,"y")&" yrs, "&DATEDIF(B2,E2,"ym")&" mths"
H2:H5	H2	=DATEDIF(EOMONTH(B2,-1),E2,"m")/12
I2:I5	I2	=IF(F2<5,"0",IF(F2<26,(F2*(C2*40)),IF(F2>=26,((C2*40)*26))))
D2:D5	D2	=C2*40

 

[edeneye]

Years of Service.xlsx
	A	B	C	D	E	F	G	H	I
1	Name	Hire Date	Hourly Pay	Weekly Pay	TERM DATE	YOS			Sev to MAX (DOH>5yrs)
2	EE1	26/10/1987	$27.69	$1,107.60	02/01/2024	36.21	36 yrs, 2 mths	36.25	28797.60
3	EE2	16/11/1998	$27.69	$1,107.60	02/01/2024	25.15	25 yrs, 1 mths	25.17	27850.83
4	EE3	24/10/2011	$27.69	$1,107.60	02/01/2024	12.20	12 yrs, 2 mths	12.25	13512.72
5	EE4	23/09/2021	$24.92	$996.80	02/01/2024	2.28	2 yrs, 3 mths	2.33	0
Sheet1

Cell Formulas
Range	Formula
F2:F5	F2	=(E2-B2)/365
G2:G5	G2	=DATEDIF(B2,E2,"y")&" yrs, "&DATEDIF(B2,E2,"ym")&" mths"
H2:H5	H2	=DATEDIF(EOMONTH(B2,-1),E2,"m")/12
I2:I5	I2	=IF(F2<5,"0",IF(F2<26,(F2*(C2*40)),IF(F2>=26,((C2*40)*26))))
D2:D5	D2	=C2*40

The G2 formula is the most correct, although I am at a loss on how to transpose this result into a number (including fraction of completed months) that can be used in another formula

 

[Joe4]

The G2 formula is the most correct, although I am at a loss on how to transpose this result into a number (including fraction of completed months) that can be used in another formula

I think this formula will give you what you need:

=DATEDIF(B2,E2,"m")/12

It returns the value show on the right on the below grid (where left column is what your column G returns):

 

[edeneye]

Duh, I think this works. I remember trying this one before, but it rounded up all of the numbers (rising overall cost) - for some reason I didn't think of expanding it to 3 decimal places for closer result

Very much appreciated!

 

[Joe4]

You are welcome.
Glad I was able to help.