Time in formulae
 

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?

Hi

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


--
Arvi Laanemets
(When sending e-mail, use address arvil<Attarkon.ee)



"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?

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?

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?

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?

Another one (just for fun)

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

Regards,

Daniel M.

"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?