Lookup Column, MATCH, return row - do some two-way math based on MATCH

[Spaceballs_the_fanboy]

Hi folks,
I think I have a very complicated way of explaining something that should be rather simple, I think.

Background: My work offers credits. You earn credits after working 10 business days in a given month.

Worksheet: I created my work sheet so that Row 1 is equal to Day 1 of the month, etc. up to Row 31. Each month is 2 columns wide, starting with January in Columns D, and E. Using January as an example, I want to put that an employee started working January 2. In the spreadsheet I indicate this with the word START in E2, E being the 2nd column utilized for the month, cell 2 being the 2nd day of the month.

My problem:
I can calculate the credits using formulas, which is rather inefficient. But, also, I do not know how to formulate if there is a second condition. The second condition is the END date. In this example, lets use March 6. To indicate this I would use the word END in I6. I do not want to have to constantly use one formula or the other depending if START, or END is used.

The solution I need, based on the formulas I know, if there is a better solution, please offer it :
I want Excel to Check columns. E, G, I, etc. for either the word START or END
If found, what is the row - the row number indicates the day of the month
If START, calculate networkdays (excluding weekends, but holidays are ok and do not need to be exluded) from START day to end of month
If END, calculate networkdays from END to beginning of the month

If networkdays is greater than, or equal to 10 = 9.375

Also... if a month is between START and END = 9.375 (I have been doing this manually)

BONUS FEATURE:
Is there a way to identify NOTnetworkdays and to fill the cell with the colour black? if it can, it would also be cool to see "holidays" filled in some colour

Thank you

 

[jasonb75]

There is a much easier way to explain what you need,

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

The bonus feature could be done with conditional formatting and a rule something like =someday=WORKDAY(someday-1,1,holidays)

The rest of it will make more sense with the easier explanation.

 

[Toadstool]

Hi Spaceballs_the_fanboy,

As Jason says it's difficult to interpret your requirements without some data and example results.

I've done the formatting for Holidays and weekends (using a grey background in case somebody overtypes) and identified the START and END rows for each month. Can you explain further?

Spaceballs_the_fan_boy.xlsx
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1																												Holiday	Date
2		Start																										New Year	1/1/2020
3																												Groundhog	2/2/2020
4																												Bat Day	4/3/2020
5						Start
6														Start
7		End
8												Start
9
10				Start
11										Start
12
13
14								Start
15
16
17
18
19
20
21
22
23
24
25						End				End		End
26
27				End
28
29
30
31
32	Jan-20	Jan-20	Feb-20	Feb-20	Mar-20	Mar-20	Apr-20	Apr-20	May-20	May-20	Jun-20	Jun-20	Jul-20	Jul-20	Aug-20	Aug-20	Sep-20	Sep-20	Oct-20	Oct-20	Nov-20	Nov-20	Dec-20	Dec-20
33		2		10		5		14		11		8		6		1		1		1		1		1
34		7		27		25		30		25		25		31		31		30		31		30		31
Sheet1

Cell Formulas
Range	Formula
F32,Z32,X32,V32,T32,R32,P32,N32,L32,J32,H32	F32	=E32+1
E32,AA32,Y32,W32,U32,S32,Q32,O32,M32,K32,I32,G32	E32	=EOMONTH(D32,0)
E33,AA33,Y33,W33,U33,S33,Q33,O33,M33,K33,I33,G33	E33	=IFERROR(MATCH("START",E$1:E$31,0),1)
E34,AA34,Y34,W34,U34,S34,Q34,O34,M34,K34,I34,G34	E34	=IFERROR(MATCH("End",E$1:E$31,0),DAY(E$32))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D1:AA31	Expression	=OR(TEXT(DATE(YEAR(D$32),MONTH(D$32),ROW()),"ddd")="Sat",TEXT(DATE(YEAR(D$32),MONTH(D$32),ROW()),"ddd")="Sun")	text	NO
D1:AA31	Expression	=INDEX($AF$2:$AF$99,MATCH(DATE(YEAR(D$32),MONTH(D$32),ROW()),$AF$2:$AF$99,0))	text	NO

 

[Spaceballs_the_fanboy]

Thank you Jason, Toadstool... It definitely would be easier to see, than for me to explain - I appreciate you showing me how to share
Here is the worksheet I have. I have NOT applied Toadstools formula to it, just you can see what my issue was with my original post.
The text at the end of the forumlas is what I am wanting to do in a single formula, or VBA, or something more efficient than multiple formulas on multiple rows (like I have been doing).
Special note: Start and End will likely not be in the same month, sorry for that confusion.

Here's hoping the work I have done makes it easier to explain what I want

Cheers!

Leave Table - Zack (2).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1			1
2		TODAY	2
3	L	2020-03-14	3
4			4
5	E		5					5600	4.500
6			6					1100	7.500
7	A		7
8			8
9	V		9
10			10				START
11	E		11
12			12
13			13
14			14
15	B		15
16			16
17	A		17			5600	0.250
18			18
19	L		19
20			20					1100	7.500
21	A		21			5600	7.500
22			22										END
23	N		23
24			24			9820	7.500
25	C		25
26			26			9820	7.500
27	E		27
28			28
29	S		29
30			30
31			31
32		Forward	DAY	January-20		February-20		March-20		April-20		May-20		June-20		July-20
33			Credit		0.000		9.375		9.375		9.375		9.375		0.000		0.000
34	Vacation	Forward	Debit		0.000		0.000		15.000		0.000		0.000		0.000		0.000
35		0.000	Balance		0.000		9.375		3.750		13.125		22.500		22.500		22.500
38			Credits		0.000		9.375		9.375		9.375		9.375		9.375		9.375
39	Sick		2200		0.000		0.000		0.000		0.000		0.000		0.000		0.000
40		Forward	2100		0.000		0.000		0.000		0.000		0.000		0.000		0.000
41		9.375	Balance		9.375		18.750		28.125		18.750		28.125		37.500		46.875
42	Appt: Med / Dental		5300		0.000		0.000		0.000		0.000		0.000		0.000		0.000
43	Family Related		All		0.000		0.000		0.000		0.000		0.000		0.000		0.000
44	Personal day		5600		0.000		7.750		4.500		0.000		0.000		0.000		0.000
45			START				START
46			END										END
47
48			FIRST DAY				2020-02-01						2020-05-01
49			DAY				10						22
50			ACTUAL DATE				2020-02-10						2020-05-22
51			LAST DAY				2020-02-29						2020-05-31
52			START or END?				19						21
53							Look up Start, or End, and return the Row Number
54							If Start - use date and count working days to end of month
55							If End - use date and count working days from beginning of month to "end" date
56							IF Start or End working days is >= 10 = 9.375, if <10 = ""
57
58
Sheet2

Cell Formulas
Range	Formula
B3	B3	=TODAY()
E34,Q34,O34,M34,K34,I34,G34	E34	=SUMIF(D$1:D$31,"1100",E$1:E$31)
E35,G35	E35	=SUM(B35+E33-E34)
G33	G33	=IF(OR(G1="START",G2="START",G3="START",G4="START",G5="START",G6="START",G7="START",G8="START",G9="START",G10="START",G11="START",G12="START",G13="START",G14="START",G15="START",G16="START",G17="START",G18="START",G19="START",G20="START",G21="START",G22="START",G23="START",G24="START",G25="START",G26="START",G27="START",G28="START",G29="START",G30="START",G31="START"),9.375,"")
I35,Q35,O35,M35,K35	I35	=SUM(G35+I33-I34)
E39,Q39,O39,M39,K39,I39,G39	E39	=SUMIF(D$1:D$31,$C$39,E$1:E$31)
E40,Q40,O40,M40,K40,I40,G40	E40	=SUMIF(D$1:D$31,$C$40,E$1:E$31)
E41	E41	=SUM(B41+E38-E39-E40)
E42,Q42,O42,M42,K42,I42,G42	E42	=SUMIF(D$1:D$31,$C$42,E$1:E$31)
E43,Q43,O43,M43,K43,I43,G43	E43	=E$96
E44,Q44,O44,M44,K44,I44,G44	E44	=E$91
E45,Q45,O45,M45,K45,I45,G45	E45	=IFERROR(VLOOKUP("START",E1:E31,1,FALSE),"")
E46,Q46,O46,M46,K46,I46,G46	E46	=IFERROR(VLOOKUP("END",E1:E31,1,FALSE),"")
G41,Q41,O41,M41,I41	G41	=SUM(E41+G38-G39-G40)
K41	K41	=SUM(B41+K38-K39-K40)
E48	E48	=IF($E$45="START",$D$32,IF($E$46="END",$D$32,""))
I48	I48	=IF($I$45="START",$H$32,IF($I$46="END",$H$32,""))
I49,G49,K49	I49	=IFERROR(MATCH(I45,I1:I31,0),"")
K48	K48	=IF($K$45="START",$J$32,IF($K$46="END",$J$32,""))
O48	O48	=IF($O$45="START",$N$32,IF($O$46="END",$N$32,""))
O49,Q49	O49	=IFERROR(MATCH(#REF!,#REF!,0),"")
Q48	Q48	=IF($Q$45="START",$P$32,IF($Q$46="END",$P$32,""))
G48	G48	=IF($G$45="START",$F$32,IF($G$46="END",$F$32,""))
G50,M50	G50	=G48+G49-1
G51,M51	G51	=EOMONTH(F32,0)
G52	G52	=G51-G50
M48	M48	=IF($M$45="START",$L$32,IF($M$46="END",$L$32,""))
M49	M49	=IFERROR(MATCH(M46,M1:M31,0),"")
M52	M52	=M50-M48

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E33,G33,E119,G119,I119,I33	Cell Value	>$K33	text	YES
U33,Y33,AA33,W33,K33,M33,O33,Q33,S33,M119,O119,Q119,S119,U119,W119,Y119,AA119	Cell Value	>$K33	text	YES