How to calculate time between to dates and exclude off time

[vcarrill]

Hello,

I am need of a formula to calculate the following:

Work order started: 1/1/2018
Time 7:00am

Work Order Completed: 1/3/2018
Time 11:00pm

The issue I have is that from 11:00pm to 7:00am, we do not have an operating shift.

I am looking to calculate how much time is being spent on work orders without the hours of 11:00pm to 7:00am being added to the total overall time.

How can I accomplish this?

Thank you!

 

[CalcSux78]

Welcome to the forum.

There are hundreds of threads on the subject. Searching for "Business Hours" I found this: https://www.mrexcel.com/forum/excel...urs-between-two-dates-excluding-weekends.html Read the link from Post #2

Excel 2010
	A	B	C
1	1/1/2018 7:00	1/3/2018 23:00	48:00:00
Sheet1

Cell Formulas
Range	Formula
C1	=(NETWORKDAYS(A1,B1)-1)*("23:00"-"7:00")+MOD(B1,1)-MOD(A1,1)

I set the formatting for C1 to [h]:mm:ss so that I could read total hours/min of the time.

 

[karthik_kaliappan]

Hi,

Do we have formula which counts the difference between two time sets and the results should be in minutes, it also should consider the weekends? To give you more perspective, we run a 24X5 support center and need to count the mins taken by the associate to respond to a customers query.

Thanks,
Karthik