I am looking for the correct formula to express the
following example:

Start Time: 11:00PM - End Time: 7:00AM = 8 Total hours
worked. (or Xh & Xmm)

Everything I've looked at thus far won't calculate
anything that occurs before midnight properly.

Thank you in advance for your help!!

Since XL stores times as fractional days, 11:00 PM is stored as
23/24ths while 7:00 AM is stored as 7/24ths.

The trick is to add 1 (i.e., 24 hours) to the later time when the
span goes across midnight. One way is to use XL's conversion of a
boolean TRUE to 1 and FALSE to 0:

A1: 11:00 PM
A2: 7:00 AM
A3: =A2 - A1 + (A2<A1)

which will evaluate as

=7/24 - 23/24 + TRUE == 7/24 - 23/24 + 1 == 8/24

You can do this a little more compactly using MOD:

A3: =MOD(A2-A1,1)

In each case, you may need to format A3 as a time.

In article ,
"Bill Smithers" wrote:

I am looking for the correct formula to express the
following example:

Start Time: 11:00PM - End Time: 7:00AM = 8 Total hours
worked. (or Xh & Xmm)

Everything I've looked at thus far won't calculate
anything that occurs before midnight properly.

Thank you in advance for your help!!

J.E.M. ...

I can't thank you enough for responding to my question. I followed your
instructions and it all works perfectly both before & after midnight
times...

Best wishes to you and your family this Holiday Season!

Bill Smithers


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!