Urgent Help Required On : Calculation of net working hours

[Upendra2016]

Hi All,

Can someone help me for the below requirement. I have already tried multiple formula's but not getting expected result.

I want to calculate net working hours between two dates considering start shift time, end shift time, start date & time, end date & time, weekends and holidays.

For Ex.
Shift Start Time : 18:30:00 PM
Shift End Time : 04:30:00 AM
Start Date & Time: 05/01/2016 8:50:00 PM
End Date & Time : 05/01/2016 11:14:02 PM
Weekend: Saturday & Sunday
Holiday: List

I have tried below formula's but not get correct result.

1. =IF(INT(D3)=INT(E3),IF(AND(WEEKDAY(D3,2)<6,COUNTIF(HoliDays,D3)=0),MEDIAN(E3,INT(E3)+Start,INT(E3)+End)-MEDIAN(D3,INT(D3)+Start,INT(D3)+End),0),MAX(WORKDAY(E3,-1,HoliDays)-WORKDAY(D3,1,HoliDays)+1,0)*9/24+IF(AND(WEEKDAY(D3,2)<6,COUNTIF(HoliDays,D3)=0),MAX(End-MOD(D3,1),0),0)+IF(AND(WEEKDAY(E3,2)<6,COUNTIF(HoliDays,E3)=0),MAX(MOD(E3,1)-Start,0),0))

2. =IF(NETWORKDAYS(D12,E12)<2,MOD(E12,1)-MOD(D12,1),($E$1-MOD(D12,1)+MOD(E12,1)-$D$1)+((NETWORKDAYS(D12,E12)-2)*10/24))

Below is some example data set
Start Date End Date
05/01/16 20:50 05/01/16 23:14
05/01/16 21:32 05/01/16 23:42
05/01/16 21:32 05/01/16 23:43
05/01/16 21:28 05/01/16 23:44
05/01/16 21:38 05/01/16 23:47
05/01/16 21:36 05/01/16 23:49
05/01/16 21:40 05/01/16 23:51
05/01/16 21:40 05/01/16 23:54
05/01/16 21:41 05/01/16 23:55
05/01/16 21:43 05/01/16 23:59
05/01/16 21:55 06/01/16 23:59
05/01/16 21:57 06/01/16 0:30
05/01/16 22:07 06/01/16 0:32
05/01/16 22:12 06/01/16 0:36
05/01/16 22:13 06/01/16 0:37
05/01/16 22:16 06/01/16 0:38
05/01/16 22:16 06/01/16 0:39
05/01/16 22:18 06/01/16 0:41
05/01/16 22:17 06/01/16 0:42
05/01/16 22:18 06/01/16 0:45
05/01/16 22:19 06/01/16 0:46
05/01/16 22:22 06/01/16 0:48
05/01/16 22:18 06/01/16 0:50
05/01/16 22:30 06/01/16 0:51
05/01/16 22:32 06/01/16 0:52
05/01/16 22:32 06/01/16 0:53
05/01/16 22:36 06/01/16 0:54
05/01/16 22:40 06/01/16 0:55

Need help on urgent basis.

It would be great if shared the excel file with formula.

If possible please explain logic.

Thanks a lot for the help in advance.

Upendra

[Upendra2016]

Hi All,

Below formula give me result on certain cases. But it will not give proper result when time end after 12:01:00 AM.

=(NETWORKDAYS(D4,E4)-1)*("18:00"-"04:30")-MOD(D4,1)+MOD(E4,1)

If start time is 05/01/2016 10:07:24 PM and end time is 06/01/2016 12:32:25 AM formula not giving result.

Please help.

[JeteMc]

Assuming that the dates are formatted day/month/year and that Start date/time is in column A and End date/time is in column B, it would seem you could just put the following formula in C2 and down:

=B2-A2

You can then format column C as custom hh:mm.
Let me know if you have any questions.

[Upendra2016]

Hi Jete,

Thanks for looking into this and for response.

Sorry! above formula not meet my requirement. I am looking for exact working hour between the two dated and time.

Column A have the value "05/01/16 20:50" and column B "05/01/16 23:14".

If the task completed in same day then I would have calculated the required hours by using your formula. But this is not the case.

Task may get completed in next one or two days and one day working hours are from 6.00 PM to 4.30 AM.

It means if a task started on 05/01/16 22:30 and ended on 06/01/16 23:30 then the net working hours are 9+1 = 10 hours.

Ex2. task started on 05/01/16 23:00 PM and ended on 06/01/16 03:00 AM then the net working hours are 04 hours.

Please let me know if you have the formula to bring the required result.

Thanks for the help in advance.

[JeteMc]

Looking at the data in the first post it seems that tasks are either finished the same shift as started or the next shift. Since a shift lasts 10 hours I used the following formula:

=IF(B2-A2>10/24,10/24-(MOD(A2,1)-18.5/24)+(MOD(B2,1)-18.5/24),B2-A2)

The column with the calculation is formatted [h]:mm:ss, and selecting Evaluate Formula from the Formulas tab will give you the details of how this works especially for row 12. My computer runs month/day/year so I am not sure the uploading the file would be helpful, however let me know if you want to see it anyway.
Let me know if you have any questions.

[Upendra2016]

Thank you very much Jete. It's working fine as per my requirement. Thanks a lot!

A Big Thanks!

[JohnTopley]

Base on JeteMc assumption:

=IF(INT(B1)=INT(A1),B1-A1,1-A1+B1)

[JeteMc]

You're Welcome and thank you for the feedback. If you haven't already, please take a moment to mark the thread 'Solved' using the thread tools link above your first post. I hope that you have a nice day.

[Upendra2016]

Hi Jete,

Sorry to get back again.

Your provided code is working for for the below conditions.

1. Task start in PM and end in AM
2. Task start in AM and end in AM
3. Task start in PM and end in PM

But it's not give correct result when start in AM and end in PM.

Also no calculation of net work days.

Please find below examples.
Start End Result
06/01/16 7:50 06/01/16 20:22 22:32:01
06/01/16 12:27 12/01/16 20:24 17:57:01

Please help to find the corrected formula.

Thanks for the help in advance.

[JeteMc]

But it's not give correct result when start in AM and end in PM.

Also no calculation of net work days.

Please find below examples.
Start End Result
06/01/16 7:50 06/01/16 20:22 22:32:01
06/01/16 12:27 12/01/16 20:24 17:57:01

Based on the initial conditions in post #1:

Shift Start Time : 18:30:00 PM
Shift End Time : 04:30:00 AM

These times don't fall in the shift. That said the following formula will give a correct time of 12:32:00 for the first case and a conditionally correct (conditioned on the start and end times listed above) time of 47:57:00 for the second and also give the same times as before for the data set in post #1:

=IF(NETWORKDAYS(A2,B2)=1,B2-A2,IF(B2-A2<=10/24,B2-A2,10/24-(MOD(A2,1)-18.5/24)+(MOD(B2,1)-18.5/24)+IF(NETWORKDAYS(A2,B2)>2,10/24*(NETWORKDAYS(A2,B2)-2))))

Let me know if you have any questions.

[Upendra2016]

Hi Lete,

Thanks for the quick response.

Sorry, result is not as per the requirement.

Considering the shift time. It's not give correct result.

Shift Start Time : 18:30:00 PM
Shift End Time : 04:30:00 AM

Please check the below result.

06/01/16 9:0606/01/16 20:2611:20:01
06/01/16 6:0206/01/16 20:2914:27:01
06/01/16 13:4206/01/16 20:3106:49:01

In first case result is suppose to be 20:26-18:30 = 01:56 where the result is 11:20.

Sorry to bother you again and again and thanks for your help in advance.

[JeteMc]

Try this modification to the formula:

=IF(NETWORKDAYS(A2,B2)=1,IF(MOD(A2,1)<18.5/24,MOD(B2,1)-18.5/24,B2-A2),IF(B2-A2<=10/24,B2-A2,10/24-(MOD(A2,1)-18.5/24)+(MOD(B2,1)-18.5/24)+MAX(0,10/24*(NETWORKDAYS(A2,B2)-2))))

Let me know if you have any questions.

[JeteMc]

Deleted by JeteMc

[Upendra2016]

Hi Jete,

Thanks for help and sorry to bother you.

Updated formula working and fulfill the requirement of working hours.

But now it's gave error when task start in AM and also ended in AM within working hours.

It will gave error for below cases.

06/01/16 0:34 AM 06/01/16 1:18 AM
06/01/16 0:38 AM 06/01/16 1:22 AM
06/01/16 0:34 AM 06/01/16 1:18 AM
05/01/16 12:38 AM 06/01/16 1:22 AM

Please help.

[Upendra2016]

Hi Jete,

Thanks for help and sorry to bother you.

Updated formula working and fulfill the requirement of working hours.

But now it's gave error when task start in AM and also ended in AM within working hours.

It will gave error for below cases.

06/01/16 0:34 AM 06/01/16 1:18 AM
06/01/16 0:38 AM 06/01/16 1:22 AM
06/01/16 0:34 AM 06/01/16 1:18 AM
05/01/16 12:38 AM 06/01/16 1:22 AM

Please help.

[JeteMc]

Try this modification to the formula:

=IF(AND((B2-A2)< 10/24,OR(A2<=INT(A2)+E$1,A2>=INT(A2)+D$1),B2<=INT(A2+1)+E$1),B2-A2,IF(AND(A2>INT(B2-1)+D$1,B2<=INT(A2+1)+E$1),B2-(INT(A2)+D$1),10/24-(MOD(A2,1)-18.5/24)+(MOD(B2,1)-18.5/24)+10/24*(NETWORKDAYS(A2,B2)-2)))

Let me know if you have any questions.

[Upendra2016]

Hi Jete,

Did you get the chance to look into this?

Please help.

[JeteMc]

Yes, the formula in post #16 is updated based on the times in post #14 about AM to AM hours.

[Upendra2016]

Hi All,

Can somebody help me on this to resolve my problem.

Thanks for the help in advance.

Upen

[JeteMc]

The formula in post #16 gives the following times for the intervals in post #15:
06/01/16 0:34 AM 06/01/16 1:18 AM 00:44:00
06/01/16 0:38 AM 06/01/16 1:22 AM 00:44:00
06/01/16 0:34 AM 06/01/16 1:18 AM 00:44:00
05/01/16 12:38 AM 06/01/16 1:22 AM 10:44:00
I believe all of those times are correct based on dates entered as dd/mm/yy and a shift that starts at 6:30 PM and ends at 4:30 AM. Are there additional time intervals for which the formula isn't working?

[Upendra2016]

Hi Jete,

Thanks for your continuous help. Sorry I am bothering you again and again.

I have tried a lot but not sure if I am using the correct formula.

I am attaching my data file here. It would be great if you could help me to put the formula in this file and send me back.

Shift Start Time : 18:30 PM
Shift End Time : 04:30 AM

Please help.

Thanks for your help in advance.

I am unable to attached the file here. So opening new request.

email ID: upendra_mahato2011@yahoo.com

[Upendra2016]

Hi All,

Is anybody get the time to look into my query?

Please help on this.

Thanks for the help in advance.

[JeteMc]

This solution makes use of four helper columns to keep track of four conditions of time input. It also assumes that Sunday is not a workday based on no received or end dates and times occurring on Sunday. The formula in column D takes care of most of the times and yields the total time if the start and end times were within the shift:

=IF(AND(OR(MOD(A3,1)<=B$1,MOD(A3,1)>=A$1),OR(MOD(B3,1)<=B$1,MOD(B3,1)>=A$1)),MOD(B3-A3,1)+MAX(IF(OR(AND(MOD(A3,1)<=B$1,MOD(B3,1)<=B$1),AND(MOD(A3,1)>=A$1,MOD(B3,1)>=A$1)),NETWORKDAYS.INTL(A3,B3,11)-1)*10/24,(NETWORKDAYS.INTL(A3,B3,11)-2)*10/24,0))-IF(AND(MOD(A3,1)<=B$1,MOD(B3,1)>=A$1),14/24,0)

The formula for column E yields the total time if the start time is before the beginning of the shift and end time is within the shift:

=IF(AND(MOD(A3,1)<A$1,MOD(A3,1)>B$1,OR(MOD(B3,1)<=B$1,MOD(B3,1)>=A$1)),MOD(MOD(B3,1)-A$1,1)+MAX((NETWORKDAYS(A3,B3,11)-2)*10/24,0))

The formula for column F yields the total time if the start time is within the shift and end time is after the end of the shift:

=IF(AND(OR(MOD(A3,1)>=A$1,MOD(A3,1)<=B$1),MOD(B3,1)>B$1,MOD(B3,1)<A$1),MOD(B$1-MOD(A3,1),1)+MAX((NETWORKDAYS(A3,B3,11)-2)*10/24,0))

The formula for column G yields the total time if the start and end times are not within the shift:

=IF(AND(MOD(A3,1)<A$1,MOD(A3,1)>B$1,MOD(B3,1)<A$1,MOD(B3,1)>B$1),MOD(B$1-A$1,1)+MAX((NETWORKDAYS(A3,B3,11)-2)*10/24,0))

The formula in column C sums the helper columns to yield the cumulative time within the shift. I imagine that someone with fresh eyes can combine the formulas. That said having the formulas separated makes it easier to isolate errors. Attached is a copy of your file with the formulas applied.
Let me know if you have any questions.