Calculate # of days/hours minus weekends

[RattlingCarp3048]

not sure how to word this exactly but basically i am trying to calculate the difference between 2 dates/times and exclude the weekends. =networkdays and =workdays isnt yielding the precision i am looking for. they only seem to be counting the whole days and not taking into account the hours.

Collection Date/Time	Order Entry Date/Time	Calander days	Calander hours	netowork days	What it should be
09/28/2022 07:00:00	10/3/22 12:22 AM	4.72 =(J2-I2)	113.37 =(J2-I2)*24	4.00 =networkdays(I2,J2)	2.72

 

[Dave Patton]

T202211a.xlsm
	D	E	F	G	H	I
3	Collection Date/Time	Order Entry Date/Time	Calander days	Calander hours		What it should be
4	Wed 28-Sep-22 07:00 AM	Mon 3-Oct-22 12:22 AM				2.72
5
8d

Cell Formulas
Range	Formula
I4	I4	=E4-D4-(NETWORKDAYS(D4,E4)-2)

 

[Dave Patton]

N.B. The start date and the end date are not 24 hours.

It may be easier to just deduct the number of weekend days.

T202211a.xlsm
	D	E	F	G	H	I
2
3	Collection Date/Time	Order Entry Date/Time	Calendar days	Calendar hours		What it should be
4	Wed 28-Sep-22 07:00 AM	Mon 3-Oct-22 12:22 AM		deduct the weekend days		2.72
5
8d

Cell Formulas
Range	Formula
I4	I4	=E4-D4-NETWORKDAYS.INTL(D4,E4,"1111100")

 

[RattlingCarp3048]

N.B. The start date and the end date are not 24 hours.

It may be easier to just deduct the number of weekend days.

T202211a.xlsm
	D	E	F	G	H	I
2
3	Collection Date/Time	Order Entry Date/Time	Calendar days	Calendar hours		What it should be
4	Wed 28-Sep-22 07:00 AM	Mon 3-Oct-22 12:22 AM		deduct the weekend days		2.72
5
8d

Cell Formulas
Range	Formula
I4	I4	=E4-D4-NETWORKDAYS.INTL(D4,E4,"1111100")

The first formula provided what i needed. i was able to apply it and they compared perfectly to what i was expecting it to.

 

[Dave Patton]

You may want to review the formulas.
Try Formulas Evaluate Formula

T202211a.xlsm
	D	E	F	G	H	I
3	Collection Date/Time	Order Entry Date/Time	Calendar days	Calendar hours
4	Mon 26-Sep-22 07:00 AM	Fri 30-Sep-22 12:22 AM				3.72
5	Mon 26-Sep-22 07:00 AM	Fri 30-Sep-22 12:22 AM				0.72
8d

Cell Formulas
Range	Formula
I4	I4	=E4-D4-NETWORKDAYS.INTL(D4,E4,"1111100")
I5	I5	=E5-D5-(NETWORKDAYS(D5,E5)-2)

 

[RattlingCarp3048]

You may want to review the formulas.
Try Formulas Evaluate Formula

T202211a.xlsm
	D	E	F	G	H	I
3	Collection Date/Time	Order Entry Date/Time	Calendar days	Calendar hours
4	Mon 26-Sep-22 07:00 AM	Fri 30-Sep-22 12:22 AM				3.72
5	Mon 26-Sep-22 07:00 AM	Fri 30-Sep-22 12:22 AM				0.72
8d

Cell Formulas
Range	Formula
I4	I4	=E4-D4-NETWORKDAYS.INTL(D4,E4,"1111100")
I5	I5	=E5-D5-(NETWORKDAYS(D5,E5)-2)

Ahh, i see. on the surface it looks ok but when i dug a little deeper i see what you mean. Good catch!