I am using Excel 2007 and trying to calculate the actual time it takes to
pick orders.
Work day starts at 06:00 and ends at 20:00. using simple [end time] -
[start time] calulation. How do i calculate the hours if the job goes in to
the next day? I am a novice, as you can tell, but desperate to learn.
Kevo

To deal with wrapping round at midnight, use =MOD(B2-A,1) and format as
time.
If you are taking more than 24 hours, then you'd have to input date and
time.
--
David Biddulph

"Kevo" wrote in message
...
I am using Excel 2007 and trying to calculate the actual time it takes to
pick orders.
Work day starts at 06:00 and ends at 20:00. using simple [end time] -
[start time] calulation. How do i calculate the hours if the job goes in
to
the next day? I am a novice, as you can tell, but desperate to learn.
Kevo

Hi David, thank you for your prompt reply.
Very helpful.
My new problem is how to "remove" the hours we don't work from 8pm at night
to 6am the next morning (10 hours we are not open).
Any suggestions?
Thanks,
Kevo

"David Biddulph" wrote:

To deal with wrapping round at midnight, use =MOD(B2-A,1) and format as
time.
If you are taking more than 24 hours, then you'd have to input date and
time.
--
David Biddulph

"Kevo" wrote in message
...
I am using Excel 2007 and trying to calculate the actual time it takes to
pick orders.
Work day starts at 06:00 and ends at 20:00. using simple [end time] -
[start time] calulation. How do i calculate the hours if the job goes in
to
the next day? I am a novice, as you can tell, but desperate to learn.
Kevo

Try this:
A1 = start time
A2 = end time

=a2-a1+(a2<a1)*time(14,0,0)

This will work as long as you finish the job within a 24-hour period.

Regards,
Fred.

"Kevo" wrote in message
...
Hi David, thank you for your prompt reply.
Very helpful.
My new problem is how to "remove" the hours we don't work from 8pm at
night
to 6am the next morning (10 hours we are not open).
Any suggestions?
Thanks,
Kevo

"David Biddulph" wrote:

To deal with wrapping round at midnight, use =MOD(B2-A,1) and format as
time.
If you are taking more than 24 hours, then you'd have to input date and
time.
--
David Biddulph

"Kevo" wrote in message
...
I am using Excel 2007 and trying to calculate the actual time it takes
to
pick orders.
Work day starts at 06:00 and ends at 20:00. using simple [end time] -
[start time] calulation. How do i calculate the hours if the job goes
in
to
the next day? I am a novice, as you can tell, but desperate to learn.
Kevo

Hi Fred, thanks for the reply and the help. I've never used this kind of site
before and its amazing how helpful and polite people sre.
Unfortunately some of our orders are on the system for more than 24 hours,
due to the complexity of stock. Is there any way to do this for times
greater than 24 hours?
Best Regards,
Kevo

"Fred Smith" wrote:

Try this:
A1 = start time
A2 = end time

=a2-a1+(a2<a1)*time(14,0,0)

This will work as long as you finish the job within a 24-hour period.

Regards,
Fred.

"Kevo" wrote in message
...
Hi David, thank you for your prompt reply.
Very helpful.
My new problem is how to "remove" the hours we don't work from 8pm at
night
to 6am the next morning (10 hours we are not open).
Any suggestions?
Thanks,
Kevo

"David Biddulph" wrote:

To deal with wrapping round at midnight, use =MOD(B2-A,1) and format as
time.
If you are taking more than 24 hours, then you'd have to input date and
time.
--
David Biddulph

"Kevo" wrote in message
...
I am using Excel 2007 and trying to calculate the actual time it takes
to
pick orders.
Work day starts at 06:00 and ends at 20:00. using simple [end time] -
[start time] calulation. How do i calculate the hours if the job goes
in
to
the next day? I am a novice, as you can tell, but desperate to learn.
Kevo

I've been looking around the discussions and trying out a few examples from
other posts. I have decided on using the following but the calculation
doesn't work on my spreadsheet, even though i can get the examples to work on
spreadsheets.
Right, I'll try to describe as best i can what the problem is:
Calculating the following difference in months, DAYS, hours and minutes.
A2=02/04/08 07:49:25
E2=12/08/08 13:05:16

trying to calculate the time difference, assuming that working hours = 14
(6am to 8pm) and only using weekdays:

=SUM((20/24-MOD(A2,1))*(WEEKDAY(A2,2)<6),MOD(E2,1)-6/24*
(WEEKDAY(E2,2)<6)+(NETWORKDAYS(A2,E2)-SUM(WEEKDAY(A2,2)
<6,WEEKDAY(E2,2)<6))*14/24)

answer i got was 2-24-01:15
2 months 24 days 1hour 15 minutes.
Obviously incorrect, but what have i done wrong?
PLEASE< PLEASE<PLEASE put me out of my misery.
Regards,
a very frustrated Englishman (and it's St Georges Day)

"Kevo" wrote:

Hi Fred, thanks for the reply and the help. I've never used this kind of site
before and its amazing how helpful and polite people sre.
Unfortunately some of our orders are on the system for more than 24 hours,
due to the complexity of stock. Is there any way to do this for times
greater than 24 hours?
Best Regards,
Kevo

"Fred Smith" wrote:

Try this:
A1 = start time
A2 = end time

=a2-a1+(a2<a1)*time(14,0,0)

This will work as long as you finish the job within a 24-hour period.

Regards,
Fred.

"Kevo" wrote in message
...
Hi David, thank you for your prompt reply.
Very helpful.
My new problem is how to "remove" the hours we don't work from 8pm at
night
to 6am the next morning (10 hours we are not open).
Any suggestions?
Thanks,
Kevo

"David Biddulph" wrote:

To deal with wrapping round at midnight, use =MOD(B2-A,1) and format as
time.
If you are taking more than 24 hours, then you'd have to input date and
time.
--
David Biddulph

"Kevo" wrote in message
...
I am using Excel 2007 and trying to calculate the actual time it takes
to
pick orders.
Work day starts at 06:00 and ends at 20:00. using simple [end time] -
[start time] calulation. How do i calculate the hours if the job goes
in
to
the next day? I am a novice, as you can tell, but desperate to learn.
Kevo