IF functions with TIME functions [Solved/Closed]

Report
-
rizvisa1
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
-
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!

6 replies

Posts
368
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
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 5790 users have said thank you to us this month

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. :)
Forgot to say, it had an excess of "( )" in the formula, after another fresh view managed to figure out. Thanks again.
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().
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
755
@Hezekiah123
Why not try some thing like
=IF(HOUR(NOW()) >=13, ((I12/100)*25)/B12 , ((I12/100)*25)/D12 )