Excel 2007 calculate time between 2 date/time columns

[Kevo]

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

 

[David Biddulph]

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.

 

[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

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

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

 

[Fred Smith]

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.

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

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

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

 

[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

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.

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

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

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

 

[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)

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

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.

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

:

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

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

 

[Fred Smith]

What you are doing wrong is not defining the problem properly. People are
answering the questions you ask, but you haven't given enough detail to
allow them to put you out of your misery.

If "2 months 24 days 1hour 15 minutes" is "obviously incorrect", what answer
do you want?
My answer would be 95 days, 5 hours and 15 minutes, but that may not be
yours.
I wouldn't measure time in months, because there's too much variability in
the length of a month, but if you insist, you need to define how you want
the months calculated.
You also need to show the answer you want when the end time is earlier than
the start time.

Specify what you need, and you'll get a quick, accurate solution. That's how
to get the most out of this group.

Regards,
Fred.

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)

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

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.

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

:

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

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

 

[Kevo]

Hi Fred, thanks for the advice. New to the discussion group (and excel
calculations), so still learning.
As you stated, the answer I would be looking for was 95 days, 5 hours and 15
minutes. I don't need months. That was just a format error.
The end time will never be earlier than the start time so that doesn't need
defining.
Thanks for all the help and advice,
Kevo

What you are doing wrong is not defining the problem properly. People are
answering the questions you ask, but you haven't given enough detail to
allow them to put you out of your misery.

If "2 months 24 days 1hour 15 minutes" is "obviously incorrect", what answer
do you want?
My answer would be 95 days, 5 hours and 15 minutes, but that may not be
yours.
I wouldn't measure time in months, because there's too much variability in
the length of a month, but if you insist, you need to define how you want
the months calculated.
You also need to show the answer you want when the end time is earlier than
the start time.

Specify what you need, and you'll get a quick, accurate solution. That's how
to get the most out of this group.

Regards,
Fred.

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)

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

:

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.

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

:

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

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

 

[Fred Smith]

The easiest way to solve any problem is to break it down into steps.
=networkdays(a2,e2) will get you the 95 days.
=mod(e2,1)-mod(a2,1) will get you the 5:15

Now the question is: what do you want to do with it?
If you simply want to display it, you can use:
=networkdays(a2,e2)& " days, "&text(mod(e2,1)-mod(a2,1),"h "" hours"" m ""
min""")

If you want to work with a number, you can use the formula:
=networkdays(a2,e2)+mod(e2,1)-mod(a2,1) which in the example equals 95.219
(days)
Unfortunately, you cannot format this to display what you want because Excel
will not display more than 31 days in a date format.

I'm curious to hear you say that "end time will never be earlier than the
start time" because this whole thread started out with you asking what to do
when a job spanned midnight -- for example, starting at 16:00 and finishing
at 10:00 the next day. You worked 14 hours per day, and you didn't want the
10 hours you were closed included in the total.

If, in fact, you want this issue addressed, tell us what you want to see
when the two times are:
A2=2008-04-02 13:05:16
E2=2008-08-12 07:49:25

Regards,
Fred.

Hi Fred, thanks for the advice. New to the discussion group (and excel
calculations), so still learning.
As you stated, the answer I would be looking for was 95 days, 5 hours and
15
minutes. I don't need months. That was just a format error.
The end time will never be earlier than the start time so that doesn't
need
defining.
Thanks for all the help and advice,
Kevo

What you are doing wrong is not defining the problem properly. People are
answering the questions you ask, but you haven't given enough detail to
allow them to put you out of your misery.

If "2 months 24 days 1hour 15 minutes" is "obviously incorrect", what
answer
do you want?
My answer would be 95 days, 5 hours and 15 minutes, but that may not be
yours.
I wouldn't measure time in months, because there's too much variability
in
the length of a month, but if you insist, you need to define how you want
the months calculated.
You also need to show the answer you want when the end time is earlier
than
the start time.

Specify what you need, and you'll get a quick, accurate solution. That's
how
to get the most out of this group.

Regards,
Fred.

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)

:

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

:

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.

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

:

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

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