Need to derive the network hours between two dates and times

[rakul_rakul]

Hi All,

I got stuck while creating the formule for the below scenario. Need your input pls.

The scenario is, I need to calculate the overall time taken to complete a task with the help of start time and end time. The start time and end time is in the format of date and time (10/11/2019 6:50:00 AM). Below are the conditions.

Our shift time starts at 07:00 AM and ends at 03:00 AM next day.
Monday 07:00 AM - Sat 03:00 AM is the time to be considered as week days.
While calculating total time, only the time between 07:00 AM - 03:00 AM should be considered i.e. 04:00 AM to 06:59:00 AM should not be added in the total hours.
Saturday 03:00:01 AM to Monday 06:59:59 AM not to be calculated i.e. only network hours to be calculated.

I created something like the below.

=NETWORKDAYS(IF(AND(MOD(A2,1)>0.125,MOD(A2,1)<0.291667),TEXT(INT(A2),"mm/dd/yyyy")&" 07:00:00 AM",A2),IF(AND(MOD(C2,1)>0.125,MOD(C2,1)<0.291667),
TEXT(INT(C2),"mm/dd/yyyy")&" 07:00:00 AM",C2))-1-MOD(IF(AND(MOD(A2,1)>0.125,MOD(A2,1)<0.291667),TEXT(INT(A2),"mm/dd/yyyy")&" 07:00:00 AM",A2),1)+
MOD(IF(AND(MOD(C2,1)>0.125,MOD(C2,1)<0.291667),TEXT(INT(C2),"mm/dd/yyyy")&" 07:00:00 AM",C2),1)

but the above does not give the desired result.

Few examples for your calculation reference.

A1 - 1/5/2019 12:23:00 AM B1 - 1/7/2019 11:15:00 AM - Total time should be 6.86

A1 - 1/12/2019 12:54:00 AM B1 - 1/14/2019 7:53:00 AM - Total time should be 2.98

A1 - 1/21/2019 1:19:00 PM B1 - 1/23/2019 12:34:00 PM - Total time should be 39.25

[AliGW]

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Almost everyone here needs help with an Excel formula ...

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[rakul_rakul]

The subject line has been changed..

[AliGW]

Much better - thanks.

[AliGW]

Is there any particular reason why you are not using the WORKDAY.INTL and NETWORKDAYS.INTL functions here? Are you still using Excel 2007?

[rakul_rakul]

Dear Ali,

No specific reason for not using .intl function. We are using version 2016.

[AliGW]

Will you please attach a small sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

Instructions (Please Read Carefully):

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment (it hasn't worked for years, and despite our repeatedly asking the technical team who own the forum to fix it, they can't be bothered to do so), so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[rakul_rakul]

Dear Ali,

Enclosed the attachment for your reference.

[AliGW]

Explain the desired results. On the right is the actual calculation - why do you want these results?

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F
1	Start Time		End Time		Desired Result (in hrs)	Actual
2	05/01/2019 00:23		07/01/2019 11:15		6.86	58:52:00
3
4	12/01/2019 00:54		14/01/2019 07:53		2.98	54:59:00
5
6	21/01/2019 13:19		23/01/2019 12:34		39.25	47:15:00

Sheet: Excel Forum Help

[rakul_rakul]

As said in the initial post, here are the working hours and non working hours.
Our shift time starts at 07:00 AM and ends at 03:00 AM next day.
Monday 07:00 AM - Sat 03:00 AM is the time to be considered as week days.
While calculating total time, only the time between 07:00 AM - 03:00 AM should be considered i.e. 03:00:01 AM to 06:59:00 AM should not be added in the total hours.
Saturday 03:00:01 AM to Monday 06:59:59 AM not to be calculated i.e. only network hours to be calculated.

Based on this calculation, the desired result is derived.

To explain in detail:- lets look at the first calculation.

Start time is 05/01/2019 00:23 - i.e. 5th Jan 12:23 AM and the end time is 07/01/2019 11:15 - i.e. 7th Jan 11:15 AM

12:23 AM - 03:00 AM - 02 hrs 37 min
The rest until Monday 06:59:00 AM is considered as weekend.
Then, Monday morning 07:00 AM - 11:15 AM - 4 hrs 15 min
hence, the total hours is 02 hrs 37 min + 4 hrs 15 min = 06 hrs 52 min

[JeteMc]

This proposal employs a number of helper columns which may be moved and/or hidden for aesthetic purposes.
Based on the premise that if the work was being done in a time zone that is 3 hrs ahead of yours, then the shift time would start at 4:00 AM and end at 12:00 AM but the number of hrs would be the same.
Start time in TZ-3: =A2-TIME(3,0,0)
End time in TZ-3: =C2-TIME(3,0,0)
Whole days worked: =NETWORKDAYS(F2,G2)-2
Hours worked on the first day: =1-MOD(F2,1)
Hours worked on the last day: =MOD(G2,1)-TIME(4,0,0)
Hours worked during whole days: =H2*20/24
Hours:Minutes =SUM(I2:K2)
Decimal hours: =L2*24 (formatted general)
Let us know if you have any questions.

[rakul_rakul]

Dear Jete,

Thanks for your response. However, I would like to get a clarification. First point is 3 hrs difference. How to work on the given date as it is in the excel sheet which I uploaded. Next point is, Network days -2. Why -2?? Lastly, whole day, first day and last day. Why not, second day, third day, etc..

Thanks for your help in advance.

[JeteMc]

First point is 3 hrs difference. How to work on the given date as it is in the excel sheet which I uploaded.

Easier to calculate duration when it doesn't span midnight. However you, or the user, would still input start and end time in your time zone (as seen in columns A and C) and let the formula worry about making the conversion.

Network days -2. Why -2??

Because the Networkday function will include the first and last day. We are separately calculating the hours worked on the partial (first and last) days of the period.

Lastly, whole day, first day and last day. Why not, second day, third day, etc..

Since know that each whole day in the period will be 20 hours and can calculate the hours worked on the two partial days of the period, we don't have to list each day.

Columns F:L may be hidden or moved to the last seven columns on the right side of the spreadsheet.
The formula from column M could be modified to read: =IF(A2="","",ROUND(L2*24,2)) ,moved to column E and then column M could be deleted.
All the user will need to see is start time (A), end time (C) and duration in hours (E).
Let us know if you have any questions.