24 hour time

[Blair]

I was wondering if its possible in Excel to calculate time based on the 24
hour clock. In other words, what I'm doing is track my start and finish time
and putting the number of hours I worked in another cell. For example, I
would start work at 2100 hours and finish at 0900 hours the following
morning, having worked 12 hours. I've been trying to setup an excel sheet so
that I can enter my start time and my finish time and have the result
automatically enter into another cell. I'm a newbie with excel, so if my
question is dumb and/or been answered a million times before, be gentle ;-).
Thank in advance for any help or suggestions.
Blair

 

[Bob Phillips]

Blair,

Format both cells and the result cell as time. Assuming they are A1 and B1,
this gives the result

=IF(A1>B1,B1-A1+1,B1-A1)

and enter your values as time, e.g. 21:00

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Blair]

Hi Bob,

Thanks for your quick response and your suggestion. Works like a charm, just
what I was looking for. Thanks again.

Blair

 

[J.E. McGimpsey]

XL stores times as fractional days, so 09:00 = 0.375 and 21:00 =
0.875. If your times span midnight, you need to add 1 to the later
time to compensate.

One way:

A1: 21:00
A2: 09:00

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

where XL will convert the boolean TRUE if A1 is "later" than A2 to a
1 and FALSE to a 0.


Another way:

A3: =MOD(A2-A1,1)

which does much the same thing, though more obscurely.

Note that entering times in XL requires a ":".

 

[Bob Phillips]

Hi Blair,

Glad to be of help.

Bob

Hi Bob,

Thanks for your quick response and your suggestion. Works like a charm, just
what I was looking for. Thanks again.

Blair


the