IF statement help

[sandston]

Hey everyone,

now I am a newbie to this site but have some background knowledge of excel. Always you come across a problem that you know is going to be simple to answer but obtaining the answer seems to evade every thought I make.

The IF statement I am using is:

=IF(K11="00:00:00","",K11)

this does not seem to work but if I enter 00:00:00 into another cell and link it that way the formula works.

any pointers would be greatly appreciated.

[benishiryo]

hi sandston, welcome to the forum. by 00:00:00, i think you meant it to be 12 am? if so, you need to remove the double quotes:
=IF(K11=0,"",K11)

by putting double quotes, you are looking for a text that says "00:00:00"

[TMS]

Well, the difference is that, when you put 00:00:00 in a cell, Excel recognises it as a time ... midnight.

But, when you put 00:00:00 in quotes in your formula, Excel sees it as a text string ... and not equal to midnight.

Try using TIME(0,0,0) or TIMEVALUE("00:00:00")


Regards, TMS

[Pete_UK]

Do it like this:

=IF(K11=--"00:00:00","",K11)

The double-minus converts it from a string to a number. You could also do it like this:

=IF(K11=0,"",K11)

Hope this helps.

Pete

[TMS]

Three different answers in three minutes and all within ten minutes of asking. That's not bad!

[sandston]

Thanks so much, like I said I knew it would be something staring me right in the face but I could not pin point what it was. I have amended the formula and is working perfectly fine. Thank everyone for such a prompt response.

@TMShucks I must agree I didn't expect a response let alone a solution so quickly thank you all again so much.

[TMS]

You're welcome. Thanks for the rep.


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save