Calculating Between time

[Violet]

I need a formula to help me calculate time in a work
schedule. For example: If John works from 9:00am to
5:00pm, I would like a formula to figure the amount of
time worked without manual calculations. Can anyone help?

 

[JE McGimpsey]

Times are stored as fractional days (e.g., 3:00 = 0.125), so you can
just subtract

A1: 9:00 AM
A2: 5:00 PM
A3: =A2-A1 ===> 8:00

when formatted as elapsed time (Format/Cells/Number/Custom [h]:mm)

Note that if your times span midnight, you need to adjust for the fact
that the "later" time will be less than the earlier time. You can take
advantage of XL's converting Boolean (TRUE/FALSE) values to 1/0,
respectively:

A3: =A2-A1+(A2<A1)

or, equivalently:

A3: =MOD(A2-A1,1)