Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
time sheet drop down lists | Excel Discussion (Misc queries) | |||
adding rows of hours and minutes to get a total | Excel Worksheet Functions | |||
Formula for a time card | Excel Worksheet Functions | |||
how to calulate time in hours cbetween two different dates? | Excel Worksheet Functions | |||
Subtract Hours | Excel Worksheet Functions |