Take time difference, but only during working hours

Status
Not open for further replies.

thankyou

Board Regular
Joined
Aug 13, 2008
Messages
189
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:

7:54
10:07
11:02
8:06

<colgroup><col></colgroup><tbody>
</tbody>

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.
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,265
Try this...

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

thankyou

Board Regular
Joined
Aug 13, 2008
Messages
189
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

Board Regular
Joined
Aug 13, 2008
Messages
189
Senior Frog! Awesome work! I guess you're also able to leap tall buildings??? Great job, and even greater appreciation.
Thank you sir.
 

thankyou

Board Regular
Joined
Aug 13, 2008
Messages
189
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,265
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

Board Regular
Joined
Aug 13, 2008
Messages
189
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,265
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

Board Regular
Joined
Aug 13, 2008
Messages
189
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.
 
Status
Not open for further replies.

Watch MrExcel Video

Forum statistics

Threads
1,099,521
Messages
5,469,124
Members
406,638
Latest member
Jack_Johnson

This Week's Hot Topics

Top