Even distribution of hours/value across multiple rows

[BGwayne]

Hello Folks,

I ran into a very weird situation, and I'm struggling to find the right solution for my problem. I spent several days searching on the net and youtube for the right solution, but nothing seems to work, and I'm totally exhausted, hence reaching out for help. Seems like there is not a straight forward solution for my problem. Although the ask seems to be pretty simple, I don't why i could not find a seemingly straight forward answer, after watching hours of youtube excel videos.

I'm trying to use Excel to auto generate timesheets for my team members, as shown below in the screenshot. User Input CELLs are highlighted in YELLOW background color, with RED font color.

Everything works, except for a 1 simple glitch, which is driving me crazy.... I want to keep my daily logging hours for each project as multipliers of 5 [1/2 half hour or 1 hr ] Like 1.50, 4.5, 2.5, 4, 3.5, 2, 0.5, 1, etc. Inorder to do that, i'm rounding odd the E8, F8, G8, USING MROUND formula in E9, F9, G9, and distribute the value across all working days evenly. When I do that the SUM total calculated for each project the bottom [E44, F44, G44], varies from the Total allocated hours for each project [ E7, F7, G7 ]. Either it is over charged, or it is under charged. I really don't know how to fix this issues.

In the attached screenshots, if you notice for Proj-1 Allocated hours is 66, but Sum of total charged hours is 67.5. So, Ideally I would prefer the last 3 billable days Dec 18th, 19th and 31st to be 4 Hrs instead of 4.5 Hrs which would bring the total to exactly 66. I'm looking for ways to do that automatically using formula, not manually.


User INPUT CELLS
Monthly - Billable days [ M5]
Monthly - Vacation days [ L5]
Individual Project Allocation % [ E6/F6/G6], Note SUM will always come to 100%
ENTIRE COLUMN A - For marking Weekends/ Vacations/ Stat HOL/ Sick Leaves, etc.

Formulated Cells
Total - Billable days in a month [ L6 = M5-L5 ]
Total - Billable Hours in a month [ M6 = L6*8 ]
Total Hrs allocated to a project in a month = E7 = M6*E6, F7 = M6*F6, G7 = M6*G6
Even Daily distribution of project Hrs : E8 = E7/L6 , F8 = F7/L6, G8 = G7/L6
Even Daily distribution of project Hrs[Rounded] : E9 = MRound(E8, 0.5) F9 = MRound(F8, 0.5), G9 = MRound(G8, 0.5)
Sum of charged project Hrs : E44 = Sum(E11:E41), F44 = Sum(F11:F41), G44 = Sum(G11:G41)

 

[myall_blues]

Are you able to share this sheet using XL2BB? it would make figuring out the issue much easier as we don't have to reproduce your data.

 

[kvsrinivasamurthy]

In column E Some entries are 0 and some 4.50. Is it a manual entry. what are the conditions.

 

[BGwayne]

Dec-24	PR Code	Fur, SL, Vac, CL	PROJ-1	PROJ-2	PROJ-3
Project Name	PROJECT-1	PROJECT 2	PROJECT 3					5.0	20.0
Total Allocation %		55%	30%	15%					15.0	120.0
Total Hrs Allocated	40.0	66.0	36.0	18.0	0.0	0.0	0.0	0.0
		Even Distribution		4.4	2.4	1.2	0	0	0	0
		Even Distribution - RND	8	4.50	2.50	1.00	0.00	0.00	0.00	0.00	Daily Total	Comments
	Day	Date
WEND	Sun	December 1, 2024	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
	Mon	December 2, 2024	0.00	4.50	2.50	1.00	0.00	0.00	0.00	0.00	8.00
	Tue	December 3, 2024	0.00	4.50	2.50	1.00	0.00	0.00	0.00	0.00	8.00
	Wed	December 4, 2024	0.00	4.50	2.50	1.00	0.00	0.00	0.00	0.00	8.00
	Thu	December 5, 2024	0.00	4.50	2.50	1.00	0.00	0.00	0.00	0.00	8.00
	Fri	December 6, 2024	0.00	4.50	2.50	1.00	0.00	0.00	0.00	0.00	8.00
WEND	Sat	December 7, 2024	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
WEND	Sun	December 8, 2024	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
	Mon	December 9, 2024	0.00	4.50	2.50	1.00	0.00	0.00	0.00	0.00	8.00
	Tue	December 10, 2024	0.00	4.50	2.50	1.00	0.00	0.00	0.00	0.00	8.00
	Wed	December 11, 2024	0.00	4.50	2.50	1.00	0.00	0.00	0.00	0.00	8.00
	Thu	December 12, 2024	0.00	4.50	2.50	1.00	0.00	0.00	0.00	0.00	8.00
	Fri	December 13, 2024	0.00	4.50	2.50	1.00	0.00	0.00	0.00	0.00	8.00
WEND	Sat	December 14, 2024	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
WEND	Sun	December 15, 2024	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
	Mon	December 16, 2024	0.00	4.50	2.50	1.00	0.00	0.00	0.00	0.00	8.00
	Tue	December 17, 2024	0.00	4.50	2.50	1.00	0.00	0.00	0.00	0.00	8.00
	Wed	December 18, 2024	0.00	4.50	2.50	1.00	0.00	0.00	0.00	0.00	8.00
	Thu	December 19, 2024	0.00	4.50	2.50	1.00	0.00	0.00	0.00	0.00	8.00
Vac	Fri	December 20, 2024	8.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	8.00	Vacation
WEND	Sat	December 21, 2024	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
WEND	Sun	December 22, 2024	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
Vac	Mon	December 23, 2024	8.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	8.00	Vacation
Vac	Tue	December 24, 2024	8.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	8.00	Vacation
HOL	Wed	December 25, 2024	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	CHRISTMAS DAY
HOL	Thu	December 26, 2024	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	BOXING DAY
Vac	Fri	December 27, 2024	8.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	8.00	Vacation
WEND	Sat	December 28, 2024	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
WEND	Sun	December 29, 2024	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
VAC	Mon	December 30, 2024	8.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	8.00	Vacation
	Tue	December 31, 2024	0.00	4.50	2.50	1.00	0.00	0.00	0.00	0.00	8.00
HOL	Wed	January 1, 2025	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	NEW YEAR'S DAY
			40.00	67.50	37.50	15.00	0.00	0.00	0.00	0.00	160.00
0.00	0.00	0.00	0.00	0.00	0.00	0.00
70.50	38.50	19.00	0.00	0.00	0.00	0.00
0.00	-1.50	-1.50	3.00	0.00	0.00	0.00	0.00	0.00

45627	PR Code	Fur, SL, Vac, CL	PROJ-1	PROJ-2	PROJ-3
Project Name	PROJECT-1	PROJECT 2	PROJECT 3	5	20
Total Allocation %		0.55	0.3	0.15	=M5-L5	=(M5-L5)*8
Total Hrs Allocated	=D9*L5	=M6*E6	=M6*F6	=M6*G6
		Even Distribution		=E7/$L$6	=F7/$L$6	=G7/$L$6
		Even Distribution - RND	8	=MROUND(E7/L6,0.5)	=MROUND(F7/L6,0.5)	=MROUND(G7/L6,0.5)	Daily Total	Comments
	Day	Date
WEND	Sun	45627	=IFS(A11="WEND",0,A11="VAC",D$9, A11="HOL",0, A11="SICK",D$9, A11="FUR",D$9, TRUE,0)	=IFS($A11="WEND",0,$A11="HOL",0,$A11="VAC",0,$A11="SICK",0,$A11="FUR",0,TRUE,E$9)	=IFS($A11="WEND",0,$A11="HOL",0,$A11="VAC",0,$A11="SICK",0,$A11="FUR",0,TRUE,F$9)	=IFS($A11="WEND",0,$A11="HOL",0,$A11="VAC",0,$A11="SICK",0,$A11="FUR",0,TRUE,G$9)	=SUM(D11:K11)
	Mon	45628	=IFS(A12="WEND",0,A12="VAC",D$9, A12="HOL",0, A12="SICK",D$9, A12="FUR",D$9, TRUE,0)	=IFS($A12="WEND",0,$A12="HOL",0,$A12="VAC",0,$A12="SICK",0,$A12="FUR",0,TRUE,E$9)	=IFS($A12="WEND",0,$A12="HOL",0,$A12="VAC",0,$A12="SICK",0,$A12="FUR",0,TRUE,F$9)	=IFS($A12="WEND",0,$A12="HOL",0,$A12="VAC",0,$A12="SICK",0,$A12="FUR",0,TRUE,G$9)	=SUM(D12:K12)
	Tue	45629	=IFS(A13="WEND",0,A13="VAC",D$9, A13="HOL",0, A13="SICK",D$9, A13="FUR",D$9, TRUE,0)	=IFS($A13="WEND",0,$A13="HOL",0,$A13="VAC",0,$A13="SICK",0,$A13="FUR",0,TRUE,E$9)	=IFS($A13="WEND",0,$A13="HOL",0,$A13="VAC",0,$A13="SICK",0,$A13="FUR",0,TRUE,F$9)	=IFS($A13="WEND",0,$A13="HOL",0,$A13="VAC",0,$A13="SICK",0,$A13="FUR",0,TRUE,G$9)	=SUM(D13:K13)
	Wed	45630	=IFS(A14="WEND",0,A14="VAC",D$9, A14="HOL",0, A14="SICK",D$9, A14="FUR",D$9, TRUE,0)	=IFS($A14="WEND",0,$A14="HOL",0,$A14="VAC",0,$A14="SICK",0,$A14="FUR",0,TRUE,E$9)	=IFS($A14="WEND",0,$A14="HOL",0,$A14="VAC",0,$A14="SICK",0,$A14="FUR",0,TRUE,F$9)	=IFS($A14="WEND",0,$A14="HOL",0,$A14="VAC",0,$A14="SICK",0,$A14="FUR",0,TRUE,G$9)	=SUM(D14:K14)
	Thu	45631	=IFS(A15="WEND",0,A15="VAC",D$9, A15="HOL",0, A15="SICK",D$9, A15="FUR",D$9, TRUE,0)	=IFS($A15="WEND",0,$A15="HOL",0,$A15="VAC",0,$A15="SICK",0,$A15="FUR",0,TRUE,E$9)	=IFS($A15="WEND",0,$A15="HOL",0,$A15="VAC",0,$A15="SICK",0,$A15="FUR",0,TRUE,F$9)	=IFS($A15="WEND",0,$A15="HOL",0,$A15="VAC",0,$A15="SICK",0,$A15="FUR",0,TRUE,G$9)	=SUM(D15:K15)
	Fri	45632	=IFS(A16="WEND",0,A16="VAC",D$9, A16="HOL",0, A16="SICK",D$9, A16="FUR",D$9, TRUE,0)	=IFS($A16="WEND",0,$A16="HOL",0,$A16="VAC",0,$A16="SICK",0,$A16="FUR",0,TRUE,E$9)	=IFS($A16="WEND",0,$A16="HOL",0,$A16="VAC",0,$A16="SICK",0,$A16="FUR",0,TRUE,F$9)	=IFS($A16="WEND",0,$A16="HOL",0,$A16="VAC",0,$A16="SICK",0,$A16="FUR",0,TRUE,G$9)	=SUM(D16:K16)
WEND	Sat	45633	=IFS(A17="WEND",0,A17="VAC",D$9, A17="HOL",0, A17="SICK",D$9, A17="FUR",D$9, TRUE,0)	=IFS($A17="WEND",0,$A17="HOL",0,$A17="VAC",0,$A17="SICK",0,$A17="FUR",0,TRUE,E$9)	=IFS($A17="WEND",0,$A17="HOL",0,$A17="VAC",0,$A17="SICK",0,$A17="FUR",0,TRUE,F$9)	=IFS($A17="WEND",0,$A17="HOL",0,$A17="VAC",0,$A17="SICK",0,$A17="FUR",0,TRUE,G$9)	=SUM(D17:K17)
WEND	Sun	45634	=IFS(A18="WEND",0,A18="VAC",D$9, A18="HOL",0, A18="SICK",D$9, A18="FUR",D$9, TRUE,0)	=IFS($A18="WEND",0,$A18="HOL",0,$A18="VAC",0,$A18="SICK",0,$A18="FUR",0,TRUE,E$9)	=IFS($A18="WEND",0,$A18="HOL",0,$A18="VAC",0,$A18="SICK",0,$A18="FUR",0,TRUE,F$9)	=IFS($A18="WEND",0,$A18="HOL",0,$A18="VAC",0,$A18="SICK",0,$A18="FUR",0,TRUE,G$9)	=SUM(D18:K18)
	Mon	45635	=IFS(A19="WEND",0,A19="VAC",D$9, A19="HOL",0, A19="SICK",D$9, A19="FUR",D$9, TRUE,0)	=IFS($A19="WEND",0,$A19="HOL",0,$A19="VAC",0,$A19="SICK",0,$A19="FUR",0,TRUE,E$9)	=IFS($A19="WEND",0,$A19="HOL",0,$A19="VAC",0,$A19="SICK",0,$A19="FUR",0,TRUE,F$9)	=IFS($A19="WEND",0,$A19="HOL",0,$A19="VAC",0,$A19="SICK",0,$A19="FUR",0,TRUE,G$9)	=SUM(D19:K19)
	Tue	45636	=IFS(A20="WEND",0,A20="VAC",D$9, A20="HOL",0, A20="SICK",D$9, A20="FUR",D$9, TRUE,0)	=IFS($A20="WEND",0,$A20="HOL",0,$A20="VAC",0,$A20="SICK",0,$A20="FUR",0,TRUE,E$9)	=IFS($A20="WEND",0,$A20="HOL",0,$A20="VAC",0,$A20="SICK",0,$A20="FUR",0,TRUE,F$9)	=IFS($A20="WEND",0,$A20="HOL",0,$A20="VAC",0,$A20="SICK",0,$A20="FUR",0,TRUE,G$9)	=SUM(D20:K20)
	Wed	45637	=IFS(A21="WEND",0,A21="VAC",D$9, A21="HOL",0, A21="SICK",D$9, A21="FUR",D$9, TRUE,0)	=IFS($A21="WEND",0,$A21="HOL",0,$A21="VAC",0,$A21="SICK",0,$A21="FUR",0,TRUE,E$9)	=IFS($A21="WEND",0,$A21="HOL",0,$A21="VAC",0,$A21="SICK",0,$A21="FUR",0,TRUE,F$9)	=IFS($A21="WEND",0,$A21="HOL",0,$A21="VAC",0,$A21="SICK",0,$A21="FUR",0,TRUE,G$9)	=SUM(D21:K21)
	Thu	45638	=IFS(A22="WEND",0,A22="VAC",D$9, A22="HOL",0, A22="SICK",D$9, A22="FUR",D$9, TRUE,0)	=IFS($A22="WEND",0,$A22="HOL",0,$A22="VAC",0,$A22="SICK",0,$A22="FUR",0,TRUE,E$9)	=IFS($A22="WEND",0,$A22="HOL",0,$A22="VAC",0,$A22="SICK",0,$A22="FUR",0,TRUE,F$9)	=IFS($A22="WEND",0,$A22="HOL",0,$A22="VAC",0,$A22="SICK",0,$A22="FUR",0,TRUE,G$9)	=SUM(D22:K22)
	Fri	45639	=IFS(A23="WEND",0,A23="VAC",D$9, A23="HOL",0, A23="SICK",D$9, A23="FUR",D$9, TRUE,0)	=IFS($A23="WEND",0,$A23="HOL",0,$A23="VAC",0,$A23="SICK",0,$A23="FUR",0,TRUE,E$9)	=IFS($A23="WEND",0,$A23="HOL",0,$A23="VAC",0,$A23="SICK",0,$A23="FUR",0,TRUE,F$9)	=IFS($A23="WEND",0,$A23="HOL",0,$A23="VAC",0,$A23="SICK",0,$A23="FUR",0,TRUE,G$9)	=SUM(D23:K23)
WEND	Sat	45640	=IFS(A24="WEND",0,A24="VAC",D$9, A24="HOL",0, A24="SICK",D$9, A24="FUR",D$9, TRUE,0)	=IFS($A24="WEND",0,$A24="HOL",0,$A24="VAC",0,$A24="SICK",0,$A24="FUR",0,TRUE,E$9)	=IFS($A24="WEND",0,$A24="HOL",0,$A24="VAC",0,$A24="SICK",0,$A24="FUR",0,TRUE,F$9)	=IFS($A24="WEND",0,$A24="HOL",0,$A24="VAC",0,$A24="SICK",0,$A24="FUR",0,TRUE,G$9)	=SUM(D24:K24)
WEND	Sun	45641	=IFS(A25="WEND",0,A25="VAC",D$9, A25="HOL",0, A25="SICK",D$9, A25="FUR",D$9, TRUE,0)	=IFS($A25="WEND",0,$A25="HOL",0,$A25="VAC",0,$A25="SICK",0,$A25="FUR",0,TRUE,E$9)	=IFS($A25="WEND",0,$A25="HOL",0,$A25="VAC",0,$A25="SICK",0,$A25="FUR",0,TRUE,F$9)	=IFS($A25="WEND",0,$A25="HOL",0,$A25="VAC",0,$A25="SICK",0,$A25="FUR",0,TRUE,G$9)	=SUM(D25:K25)
	Mon	45642	=IFS(A26="WEND",0,A26="VAC",D$9, A26="HOL",0, A26="SICK",D$9, A26="FUR",D$9, TRUE,0)	=IFS($A26="WEND",0,$A26="HOL",0,$A26="VAC",0,$A26="SICK",0,$A26="FUR",0,TRUE,E$9)	=IFS($A26="WEND",0,$A26="HOL",0,$A26="VAC",0,$A26="SICK",0,$A26="FUR",0,TRUE,F$9)	=IFS($A26="WEND",0,$A26="HOL",0,$A26="VAC",0,$A26="SICK",0,$A26="FUR",0,TRUE,G$9)	=SUM(D26:K26)
	Tue	45643	=IFS(A27="WEND",0,A27="VAC",D$9, A27="HOL",0, A27="SICK",D$9, A27="FUR",D$9, TRUE,0)	=IFS($A27="WEND",0,$A27="HOL",0,$A27="VAC",0,$A27="SICK",0,$A27="FUR",0,TRUE,E$9)	=IFS($A27="WEND",0,$A27="HOL",0,$A27="VAC",0,$A27="SICK",0,$A27="FUR",0,TRUE,F$9)	=IFS($A27="WEND",0,$A27="HOL",0,$A27="VAC",0,$A27="SICK",0,$A27="FUR",0,TRUE,G$9)	=SUM(D27:K27)
	Wed	45644	=IFS(A28="WEND",0,A28="VAC",D$9, A28="HOL",0, A28="SICK",D$9, A28="FUR",D$9, TRUE,0)	=IFS($A28="WEND",0,$A28="HOL",0,$A28="VAC",0,$A28="SICK",0,$A28="FUR",0,TRUE,E$9)	=IFS($A28="WEND",0,$A28="HOL",0,$A28="VAC",0,$A28="SICK",0,$A28="FUR",0,TRUE,F$9)	=IFS($A28="WEND",0,$A28="HOL",0,$A28="VAC",0,$A28="SICK",0,$A28="FUR",0,TRUE,G$9)	=SUM(D28:K28)
	Thu	45645	=IFS(A29="WEND",0,A29="VAC",D$9, A29="HOL",0, A29="SICK",D$9, A29="FUR",D$9, TRUE,0)	=IFS($A29="WEND",0,$A29="HOL",0,$A29="VAC",0,$A29="SICK",0,$A29="FUR",0,TRUE,E$9)	=IFS($A29="WEND",0,$A29="HOL",0,$A29="VAC",0,$A29="SICK",0,$A29="FUR",0,TRUE,F$9)	=IFS($A29="WEND",0,$A29="HOL",0,$A29="VAC",0,$A29="SICK",0,$A29="FUR",0,TRUE,G$9)	=SUM(D29:K29)
Vac	Fri	45646	=IFS(A30="WEND",0,A30="VAC",D$9, A30="HOL",0, A30="SICK",D$9, A30="FUR",D$9, TRUE,0)	=IFS($A30="WEND",0,$A30="HOL",0,$A30="VAC",0,$A30="SICK",0,$A30="FUR",0,TRUE,E$9)	=IFS($A30="WEND",0,$A30="HOL",0,$A30="VAC",0,$A30="SICK",0,$A30="FUR",0,TRUE,F$9)	=IFS($A30="WEND",0,$A30="HOL",0,$A30="VAC",0,$A30="SICK",0,$A30="FUR",0,TRUE,G$9)	=SUM(D30:K30)	Vacation
WEND	Sat	45647	=IFS(A31="WEND",0,A31="VAC",D$9, A31="HOL",0, A31="SICK",D$9, A31="FUR",D$9, TRUE,0)	=IFS($A31="WEND",0,$A31="HOL",0,$A31="VAC",0,$A31="SICK",0,$A31="FUR",0,TRUE,E$9)	=IFS($A31="WEND",0,$A31="HOL",0,$A31="VAC",0,$A31="SICK",0,$A31="FUR",0,TRUE,F$9)	=IFS($A31="WEND",0,$A31="HOL",0,$A31="VAC",0,$A31="SICK",0,$A31="FUR",0,TRUE,G$9)	=SUM(D31:K31)
WEND	Sun	45648	=IFS(A32="WEND",0,A32="VAC",D$9, A32="HOL",0, A32="SICK",D$9, A32="FUR",D$9, TRUE,0)	=IFS($A32="WEND",0,$A32="HOL",0,$A32="VAC",0,$A32="SICK",0,$A32="FUR",0,TRUE,E$9)	=IFS($A32="WEND",0,$A32="HOL",0,$A32="VAC",0,$A32="SICK",0,$A32="FUR",0,TRUE,F$9)	=IFS($A32="WEND",0,$A32="HOL",0,$A32="VAC",0,$A32="SICK",0,$A32="FUR",0,TRUE,G$9)	=SUM(D32:K32)
Vac	Mon	45649	=IFS(A33="WEND",0,A33="VAC",D$9, A33="HOL",0, A33="SICK",D$9, A33="FUR",D$9, TRUE,0)	=IFS($A33="WEND",0,$A33="HOL",0,$A33="VAC",0,$A33="SICK",0,$A33="FUR",0,TRUE,E$9)	=IFS($A33="WEND",0,$A33="HOL",0,$A33="VAC",0,$A33="SICK",0,$A33="FUR",0,TRUE,F$9)	=IFS($A33="WEND",0,$A33="HOL",0,$A33="VAC",0,$A33="SICK",0,$A33="FUR",0,TRUE,G$9)	=SUM(D33:K33)	Vacation
Vac	Tue	45650	=IFS(A34="WEND",0,A34="VAC",D$9, A34="HOL",0, A34="SICK",D$9, A34="FUR",D$9, TRUE,0)	=IFS($A34="WEND",0,$A34="HOL",0,$A34="VAC",0,$A34="SICK",0,$A34="FUR",0,TRUE,E$9)	=IFS($A34="WEND",0,$A34="HOL",0,$A34="VAC",0,$A34="SICK",0,$A34="FUR",0,TRUE,F$9)	=IFS($A34="WEND",0,$A34="HOL",0,$A34="VAC",0,$A34="SICK",0,$A34="FUR",0,TRUE,G$9)	=SUM(D34:K34)	Vacation
HOL	Wed	45651	=IFS(A35="WEND",0,A35="VAC",D$9, A35="HOL",0, A35="SICK",D$9, A35="FUR",D$9, TRUE,0)	=IFS($A35="WEND",0,$A35="HOL",0,$A35="VAC",0,$A35="SICK",0,$A35="FUR",0,TRUE,E$9)	=IFS($A35="WEND",0,$A35="HOL",0,$A35="VAC",0,$A35="SICK",0,$A35="FUR",0,TRUE,F$9)	=IFS($A35="WEND",0,$A35="HOL",0,$A35="VAC",0,$A35="SICK",0,$A35="FUR",0,TRUE,G$9)	=SUM(D35:K35)	CHRISTMAS DAY
HOL	Thu	45652	=IFS(A36="WEND",0,A36="VAC",D$9, A36="HOL",0, A36="SICK",D$9, A36="FUR",D$9, TRUE,0)	=IFS($A36="WEND",0,$A36="HOL",0,$A36="VAC",0,$A36="SICK",0,$A36="FUR",0,TRUE,E$9)	=IFS($A36="WEND",0,$A36="HOL",0,$A36="VAC",0,$A36="SICK",0,$A36="FUR",0,TRUE,F$9)	=IFS($A36="WEND",0,$A36="HOL",0,$A36="VAC",0,$A36="SICK",0,$A36="FUR",0,TRUE,G$9)	=SUM(D36:K36)	BOXING DAY
Vac	Fri	45653	=IFS(A37="WEND",0,A37="VAC",D$9, A37="HOL",0, A37="SICK",D$9, A37="FUR",D$9, TRUE,0)	=IFS($A37="WEND",0,$A37="HOL",0,$A37="VAC",0,$A37="SICK",0,$A37="FUR",0,TRUE,E$9)	=IFS($A37="WEND",0,$A37="HOL",0,$A37="VAC",0,$A37="SICK",0,$A37="FUR",0,TRUE,F$9)	=IFS($A37="WEND",0,$A37="HOL",0,$A37="VAC",0,$A37="SICK",0,$A37="FUR",0,TRUE,G$9)	=SUM(D37:K37)	Vacation
WEND	Sat	45654	=IFS(A38="WEND",0,A38="VAC",D$9, A38="HOL",0, A38="SICK",D$9, A38="FUR",D$9, TRUE,0)	=IFS($A38="WEND",0,$A38="HOL",0,$A38="VAC",0,$A38="SICK",0,$A38="FUR",0,TRUE,E$9)	=IFS($A38="WEND",0,$A38="HOL",0,$A38="VAC",0,$A38="SICK",0,$A38="FUR",0,TRUE,F$9)	=IFS($A38="WEND",0,$A38="HOL",0,$A38="VAC",0,$A38="SICK",0,$A38="FUR",0,TRUE,G$9)	=SUM(D38:K38)
WEND	Sun	45655	=IFS(A39="WEND",0,A39="VAC",D$9, A39="HOL",0, A39="SICK",D$9, A39="FUR",D$9, TRUE,0)	=IFS($A39="WEND",0,$A39="HOL",0,$A39="VAC",0,$A39="SICK",0,$A39="FUR",0,TRUE,E$9)	=IFS($A39="WEND",0,$A39="HOL",0,$A39="VAC",0,$A39="SICK",0,$A39="FUR",0,TRUE,F$9)	=IFS($A39="WEND",0,$A39="HOL",0,$A39="VAC",0,$A39="SICK",0,$A39="FUR",0,TRUE,G$9)	=SUM(D39:K39)
VAC	Mon	45656	=IFS(A40="WEND",0,A40="VAC",D$9, A40="HOL",0, A40="SICK",D$9, A40="FUR",D$9, TRUE,0)	=IFS($A40="WEND",0,$A40="HOL",0,$A40="VAC",0,$A40="SICK",0,$A40="FUR",0,TRUE,E$9)	=IFS($A40="WEND",0,$A40="HOL",0,$A40="VAC",0,$A40="SICK",0,$A40="FUR",0,TRUE,F$9)	=IFS($A40="WEND",0,$A40="HOL",0,$A40="VAC",0,$A40="SICK",0,$A40="FUR",0,TRUE,G$9)	=SUM(D40:K40)	Vacation
	Tue	45657	=IFS(A41="WEND",0,A41="VAC",D$9, A41="HOL",0, A41="SICK",D$9, A41="FUR",D$9, TRUE,0)	=IFS($A41="WEND",0,$A41="HOL",0,$A41="VAC",0,$A41="SICK",0,$A41="FUR",0,TRUE,E$9)	=IFS($A41="WEND",0,$A41="HOL",0,$A41="VAC",0,$A41="SICK",0,$A41="FUR",0,TRUE,F$9)	=IFS($A41="WEND",0,$A41="HOL",0,$A41="VAC",0,$A41="SICK",0,$A41="FUR",0,TRUE,G$9)	=SUM(D41:K41)
HOL	Wed	45658	=IFS(A42="WEND",0,A42="VAC",D$9, A42="HOL",0, A42="SICK",D$9, A42="FUR",D$9, TRUE,0)	=IFS($A42="WEND",0,$A42="HOL",0,$A42="VAC",0,$A42="SICK",0,$A42="FUR",0,TRUE,E$9)	=IFS($A42="WEND",0,$A42="HOL",0,$A42="VAC",0,$A42="SICK",0,$A42="FUR",0,TRUE,F$9)	=IFS($A42="WEND",0,$A42="HOL",0,$A42="VAC",0,$A42="SICK",0,$A42="FUR",0,TRUE,G$9)	=SUM(D42:K42)	NEW YEAR'S DAY
			=SUM(D11:D42)	=SUM(E11:E42)	=SUM(F11:F42)	=SUM(G11:G42)	=SUM(L11:L42)
=MROUND($N$11*E9,0.5)	=MROUND($N$11*F9,0.5)	=MROUND($N$11*G9,0.5)
=IF(E45>E7,0,(MROUND((E7-E45),0.5)+E9))	=IF(F45>F7,0,(MROUND((F7-F45),0.5)+F9))	=IF(G45>G7,0,(MROUND((G7-G45),0.5)+G9))
=D7-D44	=E7-E44	=F7-F44	=G7-G44	=(M6+D44)-L44

 

[BGwayne]

Are you able to share this sheet using XL2BB? it would make figuring out the issue much easier as we don't have to reproduce your data.

Hello, @myall_blues, @kvsrinivasamurthy,
I've uploaded the excel table in both values and formula format above. I'm sure you had a chance to take a look at it.

Folks, any help on this is very much appreciated. Thanks!

 

[kvsrinivasamurthy]

My question is not clarified.