Subtract time between certain days/work hours?

[lisa b.]

I need to find the time spent on a call ticket from:

7/07/06 3:30 p.m to 7/09/06 1:30 p.m.

Now, the problem is that I don't want the total days and hours. I need to
know how much time was spent on this call ticket that was opened on 7/07 at
3:30 p.m. and it was closed on 7/09 at 1:30 p.m. I can calculate it
manually.... 1 hour on 7/07 and 6.5 hours on 7/09, then it was closed... so
the total time spent on the call ticket being open is 7.5 hours

The glitch is that I only want it to calculate the time between our business
hours of 7:00 a.m. thru 4:30 p.m., not a 24 day.

Thanks, Lisa

 

[S Davis]

Make two helper cells to denote the limits of the workday. Excel uses
decimals to represent the number of hours and minutes, so you can just
set these helper cells with the correct decimal number to represent
7:30am and 4:30pm respectively. (B1 = 7:30am, C1=4:30pm)

Now add any dates that you want to the helper cells to get the limit
for that date (ie. cell A1 = 7/7/06, so A1 plus B1 will equal the
morning limit for that date, and A1 + C1 will equal the afternoon limit
for that date. Now you can just do simple math to find the difference
between the two dates within those times.

 

[S Davis]

Sorry, I should be a little more clear:

=(($C$1-B1)-($C$1-B2))

This will show the difference between end of day for date one and the
time in day one and end of day in day two and the time in day two.