VBA to calculate the amount of time that falls between the time recorded in two cells.

[jonvanwyk]

I am trying to create a formula that calculates the following:

"If C5 = X then calculate how much time between B5 and B6 is from 0700 to 1500?"

Whereas, If B5 = 0300, and B6 = 1100, then the formula above should return an answer of 4.00 because out of 0300 to 1100, 4 hours falls between 0700 to 1500. Any assistance with this is greatly appreciated!

[MarvinP]

Hi jonvanwyk,

See your answer attached. I call it time overlapping. Your problem above took a while to understand what you wanted.

[jonvanwyk]

Shift differentials are used in a timesheet I created, but right now users have to break their time up and code it depending on the hours worked. I am attempting to put together a proof of concept that would allow the form to assign the shift differential for the user. I was not sure how to say that in so few words except how my OP states it.

[jonvanwyk]

@ Marvin: I see how it works, except that the cell that contains the formula is formatted as time instead of as a number. The answer is 4 hours. Formatted as time, it returns an answer of 4:00. Formatted as a number, it returns an answer of 0.17. This is 4 hours divided by 24 hours to show that it is 17% of one day.

[MarvinP]

Hi,
Read http://www.ozgrid.com/Excel/date-time-calculations.htm where it says to multiply the cell in C5 by 24 to get a number of 4.

@jonvanwyk, I don't mean to be short or mad. Did you need the VBA to do this problem? If you do then an attached workbook would be appropriate so we can see the layout. I also don't know if your "between" parameters are fixed or can shift around. The question might be how many hours overlay between two employees if one works from time A to B and the other works from C to D. In your problem you only gave the A to B as fixed numbers and I didn't see how they were input.

[jonvanwyk]

@Marvin: No worries. My original question has been rendered moot by something revealed on a different issue. Thanks for the link though; I am sure it will come in handy on a different problem in the future.

With regard to posting the workbook; it is nearly 5mb in size, and the forum resticts it to well below that. I tried stripping it down to meeting their max size, but it essentially becomes non-functional by the time it gets that small.

Thanks again.