I am trying to calculate the hours between 22:00 and 08:00.
The e.g. below shows a worker finishes work at 14:00pm on 1/10/2008 and
starts work at 12:00pm on 2/10/2008 the next day, so their is 10 hours
between 22:00(10Pm) and 08:00(8am). The formula below does not seem to bring
back the correct result.


=IF(M46="","0",MAX($K$40-$M46,0)+MAX($K$39-$O45,0))
Result = 8 should be 10
Cell K39 = 22:00:00
Cell K40 = 8:00:00
Cell O45 = 14:00:00 1/10/2008
Cell M46 = 12:00:00 2/10/2008

Try

=B1-A1+(A1B1)

where A1 is the start time and B1 is the end time. If the end time is
earlier than the start time (e.g. start at 22:00 and end at 8:00), the
expression (A1B1) evaluates to TRUE and TRUE's numeric equivalent 1
is added to the difference. Since the value 1 is the same as 24 hours,
the function adds 1 to the end time which makes it as if the end time
is the following day.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Wed, 15 Oct 2008 15:27:02 -0700, Dave08
wrote:

I am trying to calculate the hours between 22:00 and 08:00.
The e.g. below shows a worker finishes work at 14:00pm on 1/10/2008 and
starts work at 12:00pm on 2/10/2008 the next day, so their is 10 hours
between 22:00(10Pm) and 08:00(8am). The formula below does not seem to bring
back the correct result.


=IF(M46="","0",MAX($K$40-$M46,0)+MAX($K$39-$O45,0))
Result = 8 should be 10
Cell K39 = 22:00:00
Cell K40 = 8:00:00
Cell O45 = 14:00:00 1/10/2008
Cell M46 = 12:00:00 2/10/2008

This formula is returning the total hours between finish and start times, not
the hours between 10pm and 8am.

"Chip Pearson" wrote:


Try

=B1-A1+(A1B1)

where A1 is the start time and B1 is the end time. If the end time is
earlier than the start time (e.g. start at 22:00 and end at 8:00), the
expression (A1B1) evaluates to TRUE and TRUE's numeric equivalent 1
is added to the difference. Since the value 1 is the same as 24 hours,
the function adds 1 to the end time which makes it as if the end time
is the following day.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Wed, 15 Oct 2008 15:27:02 -0700, Dave08
wrote:

I am trying to calculate the hours between 22:00 and 08:00.
The e.g. below shows a worker finishes work at 14:00pm on 1/10/2008 and
starts work at 12:00pm on 2/10/2008 the next day, so their is 10 hours
between 22:00(10Pm) and 08:00(8am). The formula below does not seem to bring
back the correct result.


=IF(M46="","0",MAX($K$40-$M46,0)+MAX($K$39-$O45,0))
Result = 8 should be 10
Cell K39 = 22:00:00
Cell K40 = 8:00:00
Cell O45 = 14:00:00 1/10/2008
Cell M46 = 12:00:00 2/10/2008

This has been cross posted to 'New Users'... Pl. refer to the solution there...
--
Always provide your feedback...


"Dave08" wrote:

This formula is returning the total hours between finish and start times, not
the hours between 10pm and 8am.

"Chip Pearson" wrote:


Try

=B1-A1+(A1B1)

where A1 is the start time and B1 is the end time. If the end time is
earlier than the start time (e.g. start at 22:00 and end at 8:00), the
expression (A1B1) evaluates to TRUE and TRUE's numeric equivalent 1
is added to the difference. Since the value 1 is the same as 24 hours,
the function adds 1 to the end time which makes it as if the end time
is the following day.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Wed, 15 Oct 2008 15:27:02 -0700, Dave08
wrote:

I am trying to calculate the hours between 22:00 and 08:00.
The e.g. below shows a worker finishes work at 14:00pm on 1/10/2008 and
starts work at 12:00pm on 2/10/2008 the next day, so their is 10 hours
between 22:00(10Pm) and 08:00(8am). The formula below does not seem to bring
back the correct result.


=IF(M46="","0",MAX($K$40-$M46,0)+MAX($K$39-$O45,0))
Result = 8 should be 10
Cell K39 = 22:00:00
Cell K40 = 8:00:00
Cell O45 = 14:00:00 1/10/2008
Cell M46 = 12:00:00 2/10/2008