Previous Topic Next Topic

IF functions with TIME functions

Solved/Closed
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 -
Hello,

I am trying to combine several IF conditions but ir appears that they are always getting an error, can someone help me?

The formula is as follows:

=IF(B2-A2>TIMEVALUE(0;20;0));TIMEVALUE(0;25;0);IF(B2-A2>=TIMEVALUE(0;0;6));(B2-A2);IF(AND(B2-A2>=TIMEVALUE(0;0;0))*(B2-A2<=TIMEVALUE(0;0;5));TIMEVALUE(0;0;0);(B2-A2)*2)))

I must have done something wrong, if anyone can point it out I would apreciate!

4 replies

Answer 1 / 4
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
 120
there are lots of error in syntax of your formula

for time value correct syntax is

=Timevalue("time text") e.g. =TIMEVALUE("0:0:0")

and for if statement it is

= IF(Logical_text, [Value_if_true], [Value_if_false])

there are "," (without "") and you used ";"
3
Answer 2 / 4

RWomanizer, thank you for your time.

I managed to figure out the problem, as you said, it was just syntax, I'm using Office 2007, the ("") in TIMEVALUE aren't needed and the "," in the IF are replaced by ";" but the you "outside view" helped me figure out the problem and the formula is working ok now, thank you once again. :)
0
Report

Forgot to say, it had an excess of "( )" in the formula, after another fresh view managed to figure out. Thanks again.
0
Answer 3 / 4
Posts
4
Registration date
Tuesday July 26, 2011
Status
Member
Last seen
July 26, 2011
I am looking for a formula to change a condition based on the time of day. Essentially, before 1pm I would like the formula to be:

=((I12/100)*25)/D12

After 1pm I would like the formula to be

=((I12/100)*25)/B12

I tried various ways with "=if(now()> ..." formulas to no avail because of the way Excel handles Now().
0
Answer 4 / 4
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 768
@Hezekiah123
Why not try some thing like
=IF(HOUR(NOW()) >=13, ((I12/100)*25)/B12 , ((I12/100)*25)/D12 )
0
Ask a question