Help with a calculation formula

[Susan T]

I need to create a spreadsheet which calculates maternity pay and am struggling.
The criteria are@
Date leave started
Weekly pay
8 weeks full pay
18 weeks half pay + 151.20
13 weeks 151.20
Can anyone help?

 

[6StringJazzer]

This is not that hard but what is the result you want? Pay for a particular week? Total pay since leave started?

 

[KRice]

Good questions,,,and welcome to the MrExcel board, Susan. Another thing to clarify, please...if maternity leave begins on, say, a Wednesday. Does the first week of maternity pay mean Wednesday-Friday...a short week (assuming a Mon-Fri work week), or does it mean something else, like Wednesday through Tuesday of the following week?

 

[Susan T]

This is not that hard but what is the result you want? Pay for a particular week? Total pay since leave started?

Hi. Weekly pay for the duration of the leave. If that makes sense!!!

 

[Susan T]

Good questions,,,and welcome to the MrExcel board, Susan. Another thing to clarify, please...if maternity leave begins on, say, a Wednesday. Does the first week of maternity pay mean Wednesday-Friday...a short week (assuming a Mon-Fri work week), or does it mean something else, like Wednesday through Tuesday of the following week?

Thanks KIrk. It will always be Monday to Friday. I dont know of that helps! Ive really managed to confuse myself!

 

[Toadstool]

Hi SusanT,

There are two things to be aware of. Let's assume weekly pay is $200:
(1). Mathematically 18 * 200/2 + 151.20 will do the multiplication first and addition last so you'll only get a single payment of 151.20. To get around this you can use parentheses which makes the items in parentheses happen first and I'm guessing the 151.20 should be paid each of the 18 weeks so it should be stated as 18 * (200/2 + 151.20).
(2). My example will assume Maternity Leave begins on a Monday. If it begins, for example, Wednesday then the first week should only pay a portion, (three fifths for the Wednesday, Thursday, Friday) and the first and last Monday to Friday weeks for each of the three rules would contain parts of the previous rule (Monday and Tuesday) plus parts of the new rule (Wednesday, Thursday, Friday).

My example shows the calculation in two ways.
Cells E2 to H2 show the total pay for each of the three rules and the grand total over the 8+18+13=39 weeks.
The other cells show how the calculation looks for each week so if you enter Maternity Leave on Monday 6 April 2020 then for that week commencing 6 April 2020 you'll get full pay $200, through the last week commencing Monday 28 December 2020 when you'll get $151.20.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Date leave started	06-Apr-20			A	B	C	Total
2	Weekly pay	$200.00			$1,600.00	$4,521.60	1965.6	$8,087.20
3
4	8 weeks full pay	Total	6-Apr	13-Apr	20-Apr	27-Apr	4-May	11-May	18-May	25-May
5	Total 8 weeks full pay	$1,600.00	$200.00	$200.00	$200.00	$200.00	$200.00	$200.00	$200.00	$200.00
6
7	18 weeks half pay + 151.20	Total	1-Jun	8-Jun	15-Jun	22-Jun	29-Jun	6-Jul	13-Jul	20-Jul	27-Jul	3-Aug	10-Aug	17-Aug	24-Aug	31-Aug	7-Sep	14-Sep	21-Sep	28-Sep
8	Total 18 weeks half pay + 151.20	$4,521.60	$251.20	$251.20	$251.20	$251.20	$251.20	$251.20	$251.20	$251.20	$251.20	$251.20	$251.20	$251.20	$251.20	$251.20	$251.20	$251.20	$251.20	$251.20
9
10	13 weeks 151.20	Total	5-Oct	12-Oct	19-Oct	26-Oct	2-Nov	9-Nov	16-Nov	23-Nov	30-Nov	7-Dec	14-Dec	21-Dec	28-Dec
11	Total 13 weeks 151.20	$1,965.60	$151.20	$151.20	$151.20	$151.20	$151.20	$151.20	$151.20	$151.20	$151.20	$151.20	$151.20	$151.20	$151.20
12
13	Grand total	$8,087.20
Sheet1

Cell Formulas
Range	Formula
E2	E2	=B2*8
F2	F2	=18*((B2/2)+(151.2))
G2	G2	=13*151.2
H2	H2	=SUM(E2:G2)
C4	C4	=B1
D10:O10,D7:T7,D4:J4	D4	=C4+7
B5	B5	=SUM(C5:J5)
C5:J5	C5	=$B$2
C7	C7	=J4+7
B8	B8	=SUM(C8:T8)
C8:T8	C8	=($B$2/2)+(151.2)
C10	C10	=T7+7
B11	B11	=SUM(C11:O11)
B13	B13	=SUM(B5:B11)