| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calcualting hours
Please help create a formula to calculate total hours serviced for a technician who conducts a service call, which takes him 3 days to complete. If the start date is 10/20/2009 10:00 a.m. and the service is completed on 10/22/2009 12:00 p.m., I would like to collect the total hours in house minus the hours the shop was closed, which is from 5 p.m. to 9 the next morning. The results should be approx. 18 hours of service.
EggHeadCafe - Software Developer Portal of Choice CSV Reader in C# http://www.eggheadcafe.com/tutorials...ader-in-c.aspx |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calcualting hours
Try
=N((INT(A11)-INT(A10)+1)*8-MAX(HOUR(A10)-9,0)-MAX(17-HOUR(A11),0)) -- __________________________________ HTH Bob <Charles Bostic wrote in message ... Please help create a formula to calculate total hours serviced for a technician who conducts a service call, which takes him 3 days to complete. If the start date is 10/20/2009 10:00 a.m. and the service is completed on 10/22/2009 12:00 p.m., I would like to collect the total hours in house minus the hours the shop was closed, which is from 5 p.m. to 9 the next morning. The results should be approx. 18 hours of service. EggHeadCafe - Software Developer Portal of Choice CSV Reader in C# http://www.eggheadcafe.com/tutorials...ader-in-c.aspx |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calcualting hours
Why the N() function, Bob?
-- David Biddulph "Bob Phillips" wrote in message ... Try =N((INT(A11)-INT(A10)+1)*8-MAX(HOUR(A10)-9,0)-MAX(17-HOUR(A11),0)) -- __________________________________ HTH Bob <Charles Bostic wrote in message ... Please help create a formula to calculate total hours serviced for a technician who conducts a service call, which takes him 3 days to complete. If the start date is 10/20/2009 10:00 a.m. and the service is completed on 10/22/2009 12:00 p.m., I would like to collect the total hours in house minus the hours the shop was closed, which is from 5 p.m. to 9 the next morning. The results should be approx. 18 hours of service. EggHeadCafe - Software Developer Portal of Choice CSV Reader in C# http://www.eggheadcafe.com/tutorials...ader-in-c.aspx |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calcualting hours
Charles,
Try this one which will do part hours and has an additional field for Holidays. If you use it create a named range called 'Holidays' and enter the dates of any days you want to exclude. It rurns decial hours/minutes if formatted as general. Remove the *24 from the end and format as [h]:mm to get time. =((NETWORKDAYS(A1,B1,Holidays)-1)*("17:00"-"09:00")+MOD(B1,1)-MOD(A1,1))*24 Mike "Charles Bostic" wrote: Please help create a formula to calculate total hours serviced for a technician who conducts a service call, which takes him 3 days to complete. If the start date is 10/20/2009 10:00 a.m. and the service is completed on 10/22/2009 12:00 p.m., I would like to collect the total hours in house minus the hours the shop was closed, which is from 5 p.m. to 9 the next morning. The results should be approx. 18 hours of service. EggHeadCafe - Software Developer Portal of Choice CSV Reader in C# http://www.eggheadcafe.com/tutorials...ader-in-c.aspx . |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calcualting hours
That was to force it into a number, otherwise it returned the result as a
timestamp. -- __________________________________ HTH Bob "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... Why the N() function, Bob? -- David Biddulph "Bob Phillips" wrote in message ... Try =N((INT(A11)-INT(A10)+1)*8-MAX(HOUR(A10)-9,0)-MAX(17-HOUR(A11),0)) -- __________________________________ HTH Bob <Charles Bostic wrote in message ... Please help create a formula to calculate total hours serviced for a technician who conducts a service call, which takes him 3 days to complete. If the start date is 10/20/2009 10:00 a.m. and the service is completed on 10/22/2009 12:00 p.m., I would like to collect the total hours in house minus the hours the shop was closed, which is from 5 p.m. to 9 the next morning. The results should be approx. 18 hours of service. EggHeadCafe - Software Developer Portal of Choice CSV Reader in C# http://www.eggheadcafe.com/tutorials...ader-in-c.aspx |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calcualting hours
I see. The other option is just to format the cell as General.
-- David Biddulph "Bob Phillips" wrote in message ... That was to force it into a number, otherwise it returned the result as a timestamp. -- __________________________________ HTH Bob "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... Why the N() function, Bob? -- David Biddulph "Bob Phillips" wrote in message ... Try =N((INT(A11)-INT(A10)+1)*8-MAX(HOUR(A10)-9,0)-MAX(17-HOUR(A11),0)) -- __________________________________ HTH Bob <Charles Bostic wrote in message ... Please help create a formula to calculate total hours serviced for a technician who conducts a service call, which takes him 3 days to complete. If the start date is 10/20/2009 10:00 a.m. and the service is completed on 10/22/2009 12:00 p.m., I would like to collect the total hours in house minus the hours the shop was closed, which is from 5 p.m. to 9 the next morning. The results should be approx. 18 hours of service. EggHeadCafe - Software Developer Portal of Choice CSV Reader in C# http://www.eggheadcafe.com/tutorials...ader-in-c.aspx |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calcualting hours
I find that even when it is General, Excel in its infinite wisdom reformats
it. I can format afterwarsd, but I end up doing it so often; N() works for me. -- __________________________________ HTH Bob "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... I see. The other option is just to format the cell as General. -- David Biddulph "Bob Phillips" wrote in message ... That was to force it into a number, otherwise it returned the result as a timestamp. -- __________________________________ HTH Bob "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... Why the N() function, Bob? -- David Biddulph "Bob Phillips" wrote in message ... Try =N((INT(A11)-INT(A10)+1)*8-MAX(HOUR(A10)-9,0)-MAX(17-HOUR(A11),0)) -- __________________________________ HTH Bob <Charles Bostic wrote in message ... Please help create a formula to calculate total hours serviced for a technician who conducts a service call, which takes him 3 days to complete. If the start date is 10/20/2009 10:00 a.m. and the service is completed on 10/22/2009 12:00 p.m., I would like to collect the total hours in house minus the hours the shop was closed, which is from 5 p.m. to 9 the next morning. The results should be approx. 18 hours of service. EggHeadCafe - Software Developer Portal of Choice CSV Reader in C# http://www.eggheadcafe.com/tutorials...ader-in-c.aspx |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| HELP w/ auto calcualting timesheet | Excel Worksheet Functions | |||
| sumif and translate annual hours into weekly quarter hours | Excel Worksheet Functions | |||
| Drivers Hours Timesheet - Calculate Hours Worked on Weekly Basis | Excel Discussion (Misc queries) | |||
| problem with calcualting | Excel Discussion (Misc queries) | |||
| Pivot table. Calcualting % from subtotals. | Excel Worksheet Functions | |||
Linear Mode
