Trouble with an IF statement

[floricita]

Hi,

I'm having trouble with the following.

I want to do a formula that says,

if cell k68 is equal to or less than 09:00, then write "0", otherwise, 09:00-time in cell k68

I did the following formula but it didn't work. either because of incorrect syntax or possibly formatting issues

Please Login or Register  to view this content.

[Bob Phillips]

Try

=IF(K68<=--"09:00",0,K68-"09:00")

[arthurbr]

First check to see if the content of K68 is time or text looking like time

To do this enter =istext(K68) in some cell. If FALSE, you have real time, if TRUE, text looking like time

If real time, I suggest entering 09:00 in a cell somewhere ( say X1) wujch will allow flexibility

Then use the following :

Please Login or Register  to view this content.

If K68 is text, let me know

[MarvinP]

Hi flocicita,

I believe you have two problems with your formula. First it is circular ref as you can't change something based on the same cell. Second I believe you need to use TimeValue("09:00") instead of what you have.

[floricita]

Thanks guys.

None of the above seems to work though-doesn't seem to return 0, if it is equal to or less than 09:00

[floricita]

Hi Yes it is text.

[floricita]

Should I try highlighting cells, format-time (hopefully stay the same)-then repeat what you suggested?

[floricita]

Right, I changed the format of the cells from custom (text) to time, and did your formula.

It works if it is greater or equal to 09:00 but for values (times) that are less, it doesn't return the 0 that I wanted, instead just a string of ######## because the date is negative value, even though I wanted it returned as 0 value.

any help would be great

Thanks

[arthurbr]

Maybe post a small sample of your data in a worksheet

[NBVC]

After you changed the format, re-type the entry in K68. It (Bob's formula) should then work.

[floricita]

Right, I have copied and pasted visible cells of what I could see originally, without changing any formats or anything

[floricita]

After you changed the format, re-type the entry in K68. It (Bob's formula) should then work.

Hi I did that and it works, if it is equal to or greater than 09:00, but if it isn't (first part of if statement, it doesn't return the 0 that I wanted. Just #######

[NBVC]

It must still be text, because only text entry will result in negative values as far as I can see.

Try selecting the cell, go to Data|Text to Columns and just click Finish. Does that change anything?

[NBVC]

Also, not sure if you noticed that Bob's formula rearranged the second part of the formula as, your request:

if cell k68 is equal to or less than 09:00, then write "0", otherwise, 09:00-time in cell k68

doesn't really make sense... you can't do 9:00 - time because you stated if time < 9:00 then 0... so if time is greater than 9:00 you will get negative.

[floricita]

Also, not sure if you noticed that Bob's formula rearranged the second part of the formula as, your request: doesn't really make sense... you can't do 9:00 - time because you stated if time < 9:00 then 0... so if time is greater than 9:00 you will get negative.

The text to columns thing didn't work.

with regards to that- basically col K is saying what times shops will open, however, we don't want shops to open no later than 09:00, so all those that are later than 09:00 I want the difference in col L and then total at the end and then also do some subsequent horizontal equations also.

So in col L I basically need the differences between 09:00 and the time in K, but if col K already states 09:00 or a lesser time, then don't bother or return value 0 (so won't effect calculations.

[NBVC]

So then use Bob's formula, in the sample you attached.....

=IF(J3<=--"09:00",0,J3-"09:00")

[floricita]

So then use Bob's formula, in the sample you attached.....

=IF(J3<=--"09:00",0,J3-"09:00")

Hi mate, I could be doing something really silly here.

But I did what you said in my sample, and it doesn't work.

It is correct if later than 09:00 but otherwise it returns############

If I don't format as time then it doesn't work either-and this is with Bob's fomrula on the sample

[floricita]

Here is the result I get

[Bob Phillips]

Try this slight revision

=IF(--J3<=--"09:00",0,J3-"09:00")

[floricita]

Ah did text to col, for J and now it works, it was the other formula that I tried earlier not this one!!!!!

Thank you everyone for your time.

[floricita]

Out of interest what does the -- part do before "09:00"?

[NBVC]

Coerces the text formatted number to an actual number....