View Single Post
  #5   Report Post  
Bob Phillips
 
Posts: n/a
Default

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?