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]

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.

Are the "v#" part of the values in column C or are they in separate columns? If they are part of column C, that will affect the sort.
In other words, is all this "v1 v2 5:13" in column C or in three columns?

 

[thankyou]

No.

That paste above represents the 3 columns in the sheet, not 1 column.

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

 

[AlphaFrog]

Does your time data (column B) have dates as seen in the formula bar (regardless of the cell format display)?

 

[thankyou]

AF, First, thanks for your patience in helping me through this. Second, I was able to figure it out on my own. Third, for you & others, here's where the error/miscommunication/answer was hiding:

I simply had to convert columns B & C to values only. B still had a formula. So did C. Best advice might have been to convert both to values only. Then sort by C. Simple.

I know that while I did substitute actual values with "v"alue, it wasn't meant to say those didn't have formulas.

==================
The following might have been the miscommunication:

AF: "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."

Me: 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.

AF: Are the "v#" part of the values in column C or are they in separate columns? If they are part of column C, that will affect the sort. In other words, is all this "v1 v2 5:13" in column C or in three columns?

FEEDBACK:

I think a better response from you, instead of "Are the v# ...." might have been:

"OK, just to be sure, before you do your sort, make sure you convert column B & C (backup the sheet, though, since it contains formulas) to "values only". Then do your sort by C. You may need to change the portion I did (C) to "General" format, which is the internal number excel stores for times / time diffs. That way your sort should work. Let me know."

At least that's how I got it to sort.

Also, a 2nd miscommunication, I feel, was when you wrote:

"You cannot sort just the formulas without the data included", which was unnecessarily confusing, since it implied that you can sort formulas "if" the data is also included. If there is a way to not have to copy and paste the column elsewhere to "values only", then that would be neat to learn.

I think giving feedback helps, even though sometimes it doesn't make everyone instantly love you, ha. So, hope you don't mind my feedback. I hope you'll value that more than some guy solving the problem on his own, not writing back, or one of those "thanks" responses that don't really add value. It's not easy giving feedback and it takes time & resources. So hope my time spent here today makes a difference.

Thanks again.

 

[AlphaFrog]

I'm glad you were able to figure it out. I very much appreciate feedback on the final solution and I certainly welcome a simple thank you. The rest of your comments are less than appreciated. I'm not looking to be a better volunteer, and your "feedback" came across as blind arrogance. I miss-communicated with you... really!

Sometimes you can sort rows with formulas, but not in this case. Your formulas reference the row below, and that relationship is screwed up when you sort. I hadn't considered that until now. But to make a blanket claim that you cannot sort formulas is incorrect. Often you can.

As far as your statement; "a better response from you", I do find that incredibly condescending even under the guise of offering meaningful feedback. You originally offered very little detail as to the nature of your data and its configuration. And when you had a sorting problem, you offered no additional detail other than the confusing and unnecessary V#'s. So I just started offering possible solutions. You do realize this is free help from a volunteer and not a corporate helpline with scripts and procedures. It's not my responsibility to tease the details from you. I would suggest that if you desire "a better response", provide a better query.

Go back and read the entire thread from a volunteer's perspective and keep in mind, I didn't have to respond at all. And the next time you feel the urge to tell someone they "miss-communicated" with you while volunteering their services... resist. That's the best tip you've gotten in this entire thread.

You're welcome.

 

[thankyou]

Mr Frog, I completely disagree but respect your opinion nonetheless. But your response was consistent with what I expected from you based on your posts thus far. It's mostly your attitude I disagree with. I mean even to call yourself "alpha frog" seems- to use your own word- condescending. Your response was entirely predictable, and yeah, of course, everyone here is helping voluntarily, just as I help people voluntarily every day. It's what I love. It seems like you do so, however, reluctantly and passive-aggressively. My feedback is that, if you want to help someone, then go all-in. As self-proclaimed alphafrog, you really don't represent others. You just represent yourself. I'm confident in the feedback I gave you. Apparently, no one else has ever given you feedback. I could tell. That's why I gave it. You're too arrogant. When the day comes where I can help others here, I'll do so humbly and wholeheartedly, and I'll encourage them in their thought processes (or solutions!). Just because you're a volunteer doesn't make you "too good for feedback". I'll always give feedback, any & every time I think it's really needed. Feedback makes a better system, and keeps it honest and for the people. The people are those you're serving, those you choose to serve. When you provide someone with help, don't be arrogant, patronizing or provide it with a constant air of reluctance, that air that you want them to feel your time is more valuable than that of the person you're here to help; just be gracious. In fact, if you look back, I was kind and made a very friendly compliment to you (able to leap tall buildings). Better to provide no help than to do so reluctantly, half-heartedly and with "I'm an alpha frog" arrogance. Everyone here is an alpha frog. They're all leaders in their own right. Your arguments were unconvincing, and, in the end, you still weren't able to help on the sort. That's ok, and actually no big deal, if it were someone else handling things graciously, with a modicum of humility in other words. More importantly, you kind of came across as "you just didn't really give a **** either way". That's the unsettling part; remember, no matter how many posts someone has, they're still human, it's never "what you do"; it's always "how you do it". Anyone can come up with a formulas, code, etc. There are tens of thousands of awesome excel helpers here. You're one of them. Since you seem to want to learn more, I just felt that your ego kind of got in the way of your ability to help, at least in this case. Well, that's just my feedback. It's not meant to hurt your feelings. Hope it helps you leap even taller buildings and makes you an even better person, thanks again.

 

[AlphaFrog]

With my 12,000+ replies over 5 years, you're the first to point out my passive-aggressive arrogance and insult me on any level. Isn't that precious. Bless your little heart.

You can just call me Frog if the Alpha prefix is too much. Better yet, don't call me anything. It's probably best if we just avoid each other.

I'll consider this thread closed, but I have a feeling you're not quite done giving me "feedback" and "respecting" my opinion.

 

[thankyou]

Again, so predictable. I was expecting you to mention the twelve k in your response. I'm sure you remind people of this constantly in your effort to be noticed. Again, it looks like you couldn't contain the arrogance and condescension even for one post in writing,

You can just call me Frog if the Alpha prefix is too much.

That's pretty sad, Frog, when your image of yourself is nothing more than a number and the name you desperately wish to be called. It seems abundantly obvious you're overcompensating for something that you feel you're lacking.

Best wishes and no need to respond ... unless you can't control yourself.

 

[barry houdini]

Hello thankyou,

I don't appreciate the tone of your last few responses - I have read through this whole thread and it seems to me that you haven't received AlphaFrog's assistance in the same spirit as it was given. Of course feedback is fine but we expect it to be courteous at all times, within the rules and spirit of MrExcel forum.

I think you have crossed the line. This thread will be closed and I expect any of your future posts to stay on the right side of our rules.

thank you