Time in formulae

[Bob Hoath]

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(A1>17: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?

 

[Arvi Laanemets]

Hi

=IF(A1>17/24,A1-17/24,0)

 

[Max]

Try instead in B1: =IF(A1>17/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

 

[Bob Phillips]

or alternatively

=MOD(A1,--("17:00"))

 

[Bob Phillips]

sorry, wrong value if a1< 17:00, try this instead

=IF(A1>--("17:00"),MOD(A1,--("17:00")),0)

 

[Daniel.M]

Another one (just for fun)

=MAX(0,A1-"17:")

Regards,

Daniel M.