Network days Help

[Spoonyz]

Hi All

Can someone help with a formula for the below? Using the network days formula (=NETWORKDAYS([@[Start date]],[@[End date]],lstHolidays)) it is calculating the amount of days but is there anyway to edit the formula so if the type of leave selected is Vacation HD then the days column shows 0.5 and not 1?

 

[Fluff]

Hi & welcome to MrExcel.
Should the numbers of days always be half if "Vacation HD" is selected?

 

[Spoonyz]

Hi & welcome to MrExcel.
Should the numbers of days always be half if "Vacation HD" is selected?

Hi

That is correct, if the type of leave selected is Vacation then days should display the whole number of days but if Vacation HD is selected then this should always be 0.5

 

[Fluff]

Ok, how about

=NETWORKDAYS([@[Start date]],[@[End date]],lstHolidays)*(IF([@[Type of leave]]="Vacation HD",0.5,1))

 

[Spoonyz]

That is still displaying 1 in the days box instead of 0.5

 

[Fluff]

Make sure there are no leading/trailing spaces in column E

 

[Dave Patton]

N.B. The named range cannot have a space.

Workdays.xlsm
	A	B	C	D	E	F	G
1
2		Start	End	Vacation	Vacation_HD		Vacation
3		1-Apr-23	10-Apr-23	2	3		2-Apr-23
4							3-Apr-23
5
6
7							Vacation_HD
8							4-Apr-23
9							5-Apr-23
10							6-Apr-23
11
5b

Cell Formulas
Range	Formula
D3	D3	=SUMPRODUCT(--(ISNUMBER(MATCH(ROW(INDIRECT(B3&":"&C3)),Vacation,0))))
E3	E3	=SUMPRODUCT(--(ISNUMBER(MATCH(ROW(INDIRECT(B3&":"&C3)),Vacation_HD,0))))

Named Ranges
Name	Refers To	Cells
Vacation	='5b'!$G$3:$G$4	D3
Vacation_HD	='5b'!$G$8:$G$10	E3

 

[Dave Patton]

A few clear examples with expected results would be helpful.
I do not know if Spoonyz read or tried the suggestions.

I made another guess and to clear the file posted the guess below.

Workdays.xlsm
	A	B	C	D	E	F	G
1
2		Start	End	Vacation	Vacation_HD		Vacation
3		1-Apr-23	10-Apr-23	1	1.5		3-Apr-23
4		1-Apr-23	10-Apr-23	1	1.5
5
6							Vacation_HD
7		1-Apr-23	10-Apr-23	2.5			4-Apr-23
8							5-Apr-23
9							6-Apr-23
10
5b

Cell Formulas
Range	Formula
D3	D3	=SUMPRODUCT(--(ISNUMBER(MATCH(ROW(INDIRECT(B3&":"&C3)),Vacation,0))))*1
E3	E3	=SUMPRODUCT(--(ISNUMBER(MATCH(ROW(INDIRECT(B3&":"&C3)),Vacation_HD,0))))*0.5
D4	D4	=NETWORKDAYS(B4,C4)-NETWORKDAYS(B4,C4,Vacation)
E4	E4	=(NETWORKDAYS(B4,C4)-NETWORKDAYS(B4,C4,Vacation_HD))*0.5
D7	D7	=LET(w,NETWORKDAYS(B7,C7),w-NETWORKDAYS(B7,C7,Vacation)+(w-NETWORKDAYS(B7,C7,Vacation_HD))*0.5)

Named Ranges
Name	Refers To	Cells
Vacation	='5b'!$G$3:$G$3	D7, D3:D4
Vacation_HD	='5b'!$G$7:$G$9	E3:E4, D7