Time Formulas
 

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!!

Time Formulas
 

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!!

Time Formulas
 

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!