Calculating leadtime subtracting specific days/times

[broeren79]

Hi all,

I have seen many questions have already been asked regarding calculating leadtimes and related topics, but I was not able to find the answer to my specific question. Therefore I am opening a new thread. I f my question has already been posed and answered, i apologize and kindly ask you to redirect me to the right thread. Thanks.

As for the question:
First some background:
I need to calculate performance against a specific target for a team that works in shifts. The working hours are in a shifted 24/5 arrangement, meaning they work from Monday 06:00 until Saturday 06:00. I need to calculate the nr of hours/minutes between start date/time (cell A1) and end date/time (cell B1) excluding non-working hours.

Due to the shifted arrangement, I have found the regular methods to do this do not work, and I have not been able to figure out how to do this.
If possible, I would like to do this without any VBA/UDF's etc, just plain vanilla Excel formulas.

I would really appreciate your help with this one.

Thanks,
Martin

 

[AliGW]

Please provide some sample data in tabular format in your next post, showing some expected outcomes.

 

[broeren79]

[table="width: 500"]
[tr]
[td]Start[/td]
[td]End[/td]
[td]gross leadtime[/td]
[td]Nett leadtime[/td]
[/tr]
[tr]
[td]8/3/2016 11:15
[/td]
[td]8/4/2016 10:16[/td]
[td]23:01:00[/td]
[td]23:01:00[/td]
[/tr]
[tr]
[td]8/4/2016 15:22
[/td]
[td]8/6/2016 04:22[/td]
[td]37:00:00[/td]
[td]37:00:00[/td]
[/tr]
[tr]
[td]7/29/2016 23:15[/td]
[td]8/1/2016 06:16[/td]
[td]55:01:00[/td]
[td]07:01:00[/td]
[/tr]
[tr]
[td]7/30/2016 04:30[/td]
[td]8/2/2016 04:35[/td]
[td]72:05:00[/td]
[td]24:05:00[/td]
[/tr]
[/table]

I hope this helps. Again, saturday 06:00 until Monday 06:00 are non-working hours. Working hours are between Monday 06:00 and Saturday 06:00.

 

[broeren79]

bumping this up with a gentil nudge

 

[AhoyNC]

Maybe this link will help.

http://www.exceltactics.com/calculate-net-work-hours-using-networkdays/

 

[broeren79]

Hi, thanks for the link. it is very useful information, but this formula depends on the assumption all working days have the same start and end time. In my case however that varies per day. On Monday, working hours are from 06:00 - 23:59. On Tuesday - Friday, working hours are 0:00 - 23:59. On Saturday, working hours are from 0:00 to 06:00.
Any ideas on how to accomodate this?

 

[broeren79]

again, a gentle nudge!

 

[AhoyNC]

Maybe this will get you started.
This formula will not work if your start and end times include more than one weekend. Say a start date of 8/3/16 and end date of 8/22/16. If this can happen then you need to figure out how many weekends are between those dates.
Also, it does not take into account holidays.

It does work for the example you gave.
The WEEKDAY function is used to check if there is a weekend between the start & stop date. So if you subtract the WEEKDAY end date from the WEEKDAY start date and the number is less than 0 then it went over a weekend. If there is a weekend then it will subtract out 48 hours.
The cells also need to be formatted as [h]:mm:ss

Hopefully this will get you started in the right direction.
Just copy formula down as needed.

Excel Workbook
	A	B	C	D
1	Start	End	gross leadtime	Nett leadtime
2	8/3/16 11:15	8/4/16 10:16	23:01:00	23:01:00
3	8/4/16 15:22	8/6/16 4:22	37:00:00	13:00:00
4	7/29/16 23:15	8/1/16 6:16	55:01:00	7:01:00
5	7/30/16 4:30	8/2/16 4:35	72:05:00	24:05:00
Sheet