24 hour time

  • Thread starter Thread starter Blair
  • Start date Start date
B

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
 
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)
 
Hi Bob,

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

Blair
 
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 ":".
 
Hi Blair,

Glad to be of help.

Bob

Blair said:
Hi Bob,

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

Blair


the
Click to expand...
 
Back
Top