sorry, wrong value if a1< 17:00, try this instead
=IF(A1--("17:00"),MOD(A1,--("17:00")),0)
--
HTH
-------
Bob Phillips
"Bob Phillips" wrote in message
...
or alternatively
=MOD(A1,--("17:00"))
--
HTH
-------
Bob Phillips
"Max" wrote in message
...
Try instead in B1: =IF(A117/24,A1-17/24,0)
Format B1 as time ("13:30" format)
Excel stores times as a fractional portion of a 24 hour day
So 20:00 is stored as "0.8333" (=20/24)
and 17:00 as "0.70833" (=17/24)
The difference: 0.8333 - 0.7083 = 0.125
which displays as: 3:00 (in time format)
For more info, do check out Chip's:
http://www.cpearson.com/excel/datetime.htm#AddingTimes
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Bob Hoath" wrote in message
...
I have a time value in a cell (A1) formatted hh:mm. Say the value is
20:00.
In cell B1 I am trying to write a formula IF(A117:00,A1-17:00,0).
This
should give me an answer 3:00 but I get error reports. I have tried
enclosing in brackets but to no avail. Any suggestions please?