• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

IF Formula with Time... having difficulty

L

LaurenL

New Member
I am trying to do a basic "IF" Formula, the format of the cells are in custom [h]:mm and I need to keep this format: I am trying to add in a formula =If the cell value is greater than 20:00 than the return is "no" and if the cell value is less than 20:00 than the return should be "yes". Keep getting error "found problem with formula... I know it has to do with the formatting, any suggestions?
 
Are you trying to hard-code the 20:00 value in the formula?

Perhaps you could put 20:00 in another cell (C1, for example), and then write a formula like so:

=IF(A1>$C$1,"no","yes")

attachment
 

Attachments

  • lauren1.xlsx
    8.3 KB · Views: 2
Hard code is when static/constant is used in formula (note that except for numbers it should be in quotation).
Ex: =IF(A1>"20:00","no","yes")
However, above will treat 20:00 as text and will not yield expected result.
If it's not in quotation, then it will give syntax error as ":" is used to denote range in Excel.

If using time as hard code, it needs to be nested in VALUE() function, which will convert text to numeric value that Excel can work with (when possible, otherwise it will return error).
Ex: =IF(A1>VALUE("20:00"),"no","yes")
 
You must log in or register to reply here.
Back
Top