Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 138
Default Subtract time between certain days/work hours?

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


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 138
Default Subtract time between certain days/work hours?

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
time sheet drop down lists Steve Excel Discussion (Misc queries) 12 March 18th 06 11:30 PM
adding rows of hours and minutes to get a total Tipps Excel Worksheet Functions 1 November 4th 05 08:03 PM
Formula for a time card skateblade Excel Worksheet Functions 6 November 2nd 05 10:28 PM
how to calulate time in hours cbetween two different dates? shonkoo Excel Worksheet Functions 3 October 30th 05 04:30 AM
Subtract Hours Erika Excel Worksheet Functions 5 July 20th 05 04:29 PM


All times are GMT +1. The time now is 02:31 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"