Calculate Time between 2 times but excluding a specific time range

[corramk]

Hi, After looking through loads of previous posts and seen your answers for some othe questions, I'm sure some one can help me with this problem I have.

I want to be able to calculate the amount of time taken to complete a task. Now the task length could span into several days, however I only want to calculate the time taken to complete the task between 0900-1700.

Example: Task started at 11:30 on the 01/01/2012 and completed at 16:30 on 02/01/2012 (dd/mm/yyyy), what is the length of time to complete job excluding the hours between 1700 on day one and 0900 on day two?

Not sure how to best formulate this! Anyone with any ideas?

Thanks

 

[GorD]

Try this, which i was helped with a while back, adapted to your example

Format result cell as [h]:mm


=(NETWORKDAYS(A2,B2))*("17:00"-"9:00")+MOD(B2,1)-MOD(A2,1)

Excel 2007
	A	B	C	D
1	Start	Complete
2	01/01/2012 11:30	02/01/2012 16:30	13:00	hrs
3	01/01/2012 12:00	02/01/2012 10:00	6:00	hrs
Sheet1

 

[corramk]

Thanks this is good and almost does the job! However line 4 below is not calculated correctly as the start time is before 09:00 and I don't want the clock to start counting again until 09:00 so the answer for line 4 should be 9 hours!

It seems to work for any start time after 0900 on the first day, but I have start times before 0900 and don't want the time before 9 included! I was thinking of doing this in VBA, but not had anyluck so far, any other thoughts? Thanks

[TABLE="width: 589"]
<TBODY>[TR]
[TD] [/TD]
[TD]Start</SPAN>[/TD]
[TD]Complete</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2</SPAN>[/TD]
[TD]01/01/2012 11:00</SPAN>[/TD]
[TD]02/01/2012 16:00</SPAN>[/TD]
[TD]13:00</SPAN>[/TD]
[TD]hrs</SPAN>[/TD]
[/TR]
[TR]
[TD]3</SPAN>[/TD]
[TD]01/01/2012 12:00</SPAN>[/TD]
[TD]02/01/2012 10:00</SPAN>[/TD]
[TD]06:00</SPAN>[/TD]
[TD]hrs</SPAN>[/TD]
[/TR]
[TR]
[TD]4</SPAN>[/TD]
[TD]01/01/2012 08:00</SPAN>[/TD]
[TD]02/01/2012 10:00</SPAN>[/TD]
[TD]10:00</SPAN>[/TD]
[TD]hrs</SPAN>[/TD]
[/TR]
[TR]
[TD]5</SPAN>[/TD]
[TD]01/01/2012 11:00</SPAN>[/TD]
[TD]01/01/2012 16:00</SPAN>[/TD]
[TD]05:00</SPAN>[/TD]
[TD]hrs</SPAN>[/TD]
[/TR]
[TR]
[TD]6</SPAN>[/TD]
[TD]01/01/2012 11:00</SPAN>[/TD]
[TD]03/01/2012 16:00</SPAN>[/TD]
[TD]21:00</SPAN>[/TD]
[TD]hrs</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL span=2><COL span=2></COLGROUP>[/TABLE]

 

[GorD]

Try amending formula to

=(NETWORKDAYS(A4,B4))*("17:00"-"9:00")+MOD(B4,1)-MAX(MOD(A4,1),0.375)

The max statement should kick in if the start time is pre 9am ( 9am = .375 of a day)

 

[barry houdini]

What are the possible start/end times dates, you say the start time can be before 09:00, could the end time be after 17:00 or either start or end date be at the weekend?

 

[GorD]

Ah Barry - you taught me that formula/approach a few weeks back - I was muddling my way through, which probably showed

 

[barry houdini]

I think your formula works OK if the only "out of hours" times are starts before 09:00....but you need a -1 in there too...

=(NETWORKDAYS(A4,B4)-1)*("17:00"-"9:00")+MOD(B4,1)-MAX(MOD(A4,1),0.375)

 

[GorD]

I started off with a -1, but couldn't get it to calculate correctly with it in - so took it out.

I still can't it to work with with the -1.??

 

[barry houdini]

It should work OK for working days, in your earlier example you used 1/1/2012 as the start date but that's a Sunday

If you have today as the start date (a Thursday) and tomorrow as the end date then NETWORKDAYS will return 2 but we really want to count that as 1 day, hence the -1

 

[corramk]

What are the possible start/end times dates, you say the start time can be before 09:00, could the end time be after 17:00 or either start or end date be at the weekend?

Oopps, sorry, thought I had put that in the brief :s. The start/end time and date could be anytime time or date and could be on a weekend i.e. 02/04/11 07:30 to 05/04/11 13:00 or 06/06/12 10:00 to 06/06/12 16:34 or 23/01/11 18:23 to 23/01/11 20:35 and so on.

I want the elapsed time calculated between 9-1700 Monday to Friday only. Where the start/end time and date spand into the following or couple of days or goes over a weekend I want the count to stop at 1700 each day and continue at 0900 the following day (if a Friday to Monday then stops 1700 on Friday and start again on Monday at 0900).

I hope this is clearer, thanks to all who are working on this