Formula Incorporated with =WORKDAY

[Gks77117]

Currently with my corporation - I am looking to add in pre-existing stats into Excel and have it auto-calculate a result. I already have the calculation, numbers, result, etc. but need assistance with adding this into an excel formula.

Here is a rough outline (please let me know where I can clarify any confusion):

The total excel would contain 4 columns. with the 5th column being the final result.

1. Manual entry of total number of working hours in current month (example: February 2025 = 152).
2. Manual entry of the number of days said associate was out of office (as of date report is run, i.e. today's date).
3. Manual entry of number of closed cases as of said date.
4. Manual entry of remaining working days in month.

The last 5th column would be the final number (formula based on outline below).

Number of working hours in month (Column 1). minus out of office hours (Column 2). This result multiplied by 0.875 (departments shrinkage). Take this new result, divide by 8 (Hours per day) and multiply by 6 (case closure goal per day). This gives the current closed cases goal by end of month. Subtract the number of currently closed cases from this new number and DIVIDE by number of working DAYS remaining in month.


I apologize for this being lengthy, any assistance is appreciated !!

 

[Eric W]

Welcome to the MrExcel forum!

Try:

Book1
	A	B	C	D	E	F	G	H
1	Name	Work hours in February	OOO days	Closed cases	Remaining work days	Formula		Holidays
2	Associate 1	152	3	95	5	-2.2		1/1/2025
3								1/20/2025
4								2/17/2025
5								5/26/2025
6								6/19/2025
7								7/4/2025
8								9/1/2025
9								10/13/2025
10								11/11/2025
11								11/27/2025
12								12/25/2025
Sheet2

Cell Formulas
Range	Formula
B1	B1	="Work hours in "&TEXT(TODAY(),"mmmm")
B2	B2	=NETWORKDAYS(TODAY()-DAY(TODAY())+1,EOMONTH(TODAY(),0),$H$2:$H$12)*8
E2	E2	=NETWORKDAYS(TODAY(),EOMONTH(TODAY(),0),$H$2:$H$12)
F2	F2	=(((B2-C2*8)*0.875/8*6)-D2)/E2

I came up with formulas to automatically calculate the work days in this month, and remaining days, which uses the Holidays table.