Time Formulas

  • Thread starter Thread starter Bill Smithers
  • Start date Start date
B

Bill Smithers

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.
 
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
 
Back
Top