Hours functions

[vmjan02]

I have this functions and its not giving the required result, is there a other way to fix it. any other function will be great.

note:- excel version is 2016 and higher, so functions need to be working in all versions

=IF(A2="RRC",((NETWORKDAYS.INTL(B2,C2,16,0)-1)*("23:00"-"08:00")+MEDIAN(MOD(C2,1),"23:00","08:00")-MEDIAN(MOD(B2,1),"23:00","08:00"))*24,IF(A2="DC",((NETWORKDAYS.INTL(B2,C2,16,0)-1)*("23:00"-"09:00")+MEDIAN(MOD(C2,1),"23:00","09:00")-MEDIAN(MOD(B2,1),"23:00","09:00"))*24,IF(A2="Quality",((NETWORKDAYS.INTL(B2,C2,16,0)-1)*("22:00"-"10:00")+MEDIAN(MOD(C2,1),"22:00","10:00")-MEDIAN(MOD(B2,1),"22:00","10:00"))*24,(C2-B2)*24)))

Book2
	A	B	C	D	E
1	TASK TYPE	Create Date	Update Date	Task Duration Business Hours	Actual
2	RRC	10/21/2023 20:20	10/21/2023 21:49	1.5
3	Filtration	10/21/2023 19:57	10/21/2023 21:49	1.9
4	RRC	10/14/2023 15:17	10/15/2023 11:06	10.8
5	Filtration	10/14/2023 15:07	10/15/2023 11:06	20.0
6	Filtration	10/20/2023 14:24	10/20/2023 14:24	0.0
7	Filtration	10/25/2023 0:21	10/25/2023 0:21	0.0
8	Filtration	10/16/2023 13:18	10/16/2023 13:18	0.0
9	RRC	10/20/2023 10:06	10/20/2023 12:28	-12.6	this cannot be -ve it should be 2.2(hrs)
10	DC	10/19/2023 19:41	10/20/2023 18:43	-1.0	this cannot be -ve it should be
11	DC	10/13/2023 4:18	10/13/2023 16:43	-6.3	this cannot be -ve it should be 12.2(hrs)
12	DC	10/13/2023 15:18	10/13/2023 21:10	-8.1	this cannot be -ve
13	RRC	10/13/2023 14:22	10/13/2023 21:10	-8.2	this cannot be -ve
Sheet1

Cell Formulas
Range	Formula
D2:D13	D2	=IF(A2="RRC",((NETWORKDAYS.INTL(B2,C2,16,0)-1)*("23:00"-"08:00")+MEDIAN(MOD(C2,1),"23:00","08:00")-MEDIAN(MOD(B2,1),"23:00","08:00"))*24,IF(A2="DC",((NETWORKDAYS.INTL(B2,C2,16,0)-1)*("23:00"-"09:00")+MEDIAN(MOD(C2,1),"23:00","09:00")-MEDIAN(MOD(B2,1),"23:00","09:00"))*24,IF(A2="Quality",((NETWORKDAYS.INTL(B2,C2,16,0)-1)*("22:00"-"10:00")+MEDIAN(MOD(C2,1),"22:00","10:00")-MEDIAN(MOD(B2,1),"22:00","10:00"))*24,(C2-B2)*24)))

 

[jkpieterse]

I think the issue has to do with the fact that October 20 is a Friday and you've told NETWORKDAYS that is a weekend.

 

[vmjan02]

I think the issue has to do with the fact that October 20 is a Friday and you've told NETWORKDAYS that is a weekend.

ok, but if we exclude week end, cna this be fixed, as i tried many ways, also the end user will be using lower version 2016.

please suggest.

 

[vmjan02]

Hi any update on this please

 

[jkpieterse]

I don;t have time today. Rather than having us dissect the formula, could you state the business rules for how duration must be calculated?