Subtract time between certain days/work hours?

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

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.

lisa b. wrote:
> 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

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.


S Davis wrote:
> 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.
>
> lisa b. wrote:
> > 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