Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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? |
#5
|
|||
|
|||
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? |
#6
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
entering numbers to display a time format | Excel Discussion (Misc queries) | |||
Help - Information with time and date | Excel Discussion (Misc queries) | |||
time sheet | Excel Worksheet Functions | |||
Round Time | Excel Worksheet Functions | |||
time formatting and time categorizing (vlookup or data validation) | Excel Worksheet Functions |