Take time difference, but only during working hours

[thankyou]

Working hours would be defined as starting 6:30am, and finishing 1:15pm

I have a bunch of times in one column. I want to see how long each time segment is. Example:

[TABLE="width: 239"]
<colgroup><col></colgroup><tbody>[TR]
[TD]7:54
[/TD]
[/TR]
[TR]
[TD]10:07
[/TD]
[/TR]
[TR]
[TD]11:02[/TD]
[/TR]
[TR]
[TD]8:06
[/TD]
[/TR]
</tbody>[/TABLE]

All that data is in Column B. Need your help to do this:

Formula should go in C2 and "basically" take the difference between B3-B2 but only adds the work hours. So, if I work from 11:02 on day 1 to 8:30am the next day, I worked 3h49m or with proper formatting: 3:49 is the true difference. That's what I need the formula to be able to do on the overnight shifts. Here's more info if it clarifies:

You can see taking the difference between 7:54 and 10:07, and also the difference between 10:07-11:02 is easy. The one I need your help with please is this: In going from 11:02 to 8:06, my shift time length would be:

(1:15pm - 11:02) + (8:06 - 6:30) = 3h49min or "3:49".

So, that part in bold represents the idea I need to formulate with your help for time segments that go from one day to the next.

Another way to put it is that for successive times that go from (some time before 1:15pm on day 1) to (some time after 6:30am on day 2) should have that more complex formula. Otherwise, it's just the cell-to-cell difference.

Thanks for helping out.

 

[AlphaFrog]

Try this...

=IF(B3="","",B3-B2+(B2>B3)*TIME(6,45,0))

 

[thankyou]

Working on that now...thx...

 

[thankyou]

you may not believe it but I was just logging in to say something like "maybe it involves an "IF..." type of statement...which is exactly what you've got...I'll be right back with the news Senior!

 

[thankyou]

Senior Frog! Awesome work! I guess you're also able to leap tall buildings??? Great job, and even greater appreciation.
Thank you sir.

 

[thankyou]

Snr, May I kindly ask this follow-up....I have my time diffs formatted like "h:mm", and I'm wondering if that might be why things aren't sorting properly according to those values? thanks if you know. kind regards

 

[AlphaFrog]

You're welcome.

The cell format doesn't affect the sort. What are you sorting on and what is the problem? I cannot diagnose; "things aren't sorting properly".

 

[thankyou]

Snr, If I sort by that column of time diffs, which is column C here, from largest to smallest, the table doesn't sort.

Example (already sorted):

v1 v2 5:13
v3 v4 1:48
v5 v6 6:37 <<<< SHOULD be first in the sort when I sort by Column C. What do you think?
v7 v8 0:23
v9 v10 5:56 <<<< should be 2nd in the sort
v11 v12 5:38 <<<< should be 3rd in the sort
v13 v14 3:14
v15 v16 4:27
v17 v18 1:27

The sort should have 6:37 (the largest number)'s row on top, followed by 5:56's row, etc, but you might notice that the sort is incorrect. v1 thru v18 are just values that are unimportant for this question.

I'm wondering why the sort is incorrect. For what it's worth, the values in column C (5:13, etc) are formatted as custom: h:mm.

Thank you

 

[AlphaFrog]

Are you including column B (the time data) when you sort on column C? You cannot sort just the formulas without the data included otherwise the formulas would just recalculate the unsorted data.

 

[thankyou]

Are you including column B (the time data) when you sort on column C? You cannot sort just the formulas without the data included otherwise the formulas would just recalculate the unsorted data.

Thanks for your response. Assuming I understand you right, yes, I just sort the whole sheet (just 3 columns) by value from largest to smallest according to Column C calculated values. So, yes, I do include Column B as well, sorting the rows of the sheet according to the values in C.