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

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

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

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
.

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

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

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