CODEWITHSUNDEEP
excelfunction
Rom
Rom

Reputation: 17

How to combine IF and TEXT in a function?

Currently I have row A displaying the day of the week based on the date in row B.

I am using the function:

=TEXT(B:B, "dddd")

However, this defaults to displaying "Saturday" when there is no date in the cell next to it. I would like to have it display nothing at all when there is no date. I'm thinking I need to use IF for this, but don't understand how to.

This is what I have so far:

=IF(B:B<>"", "TEXT(B:B, "dddd")", " ")

but this doesn't do anything.

Any help would be appreciated.

Upvotes: 0

Views: 420

Answers (1)

user3943127
user3943127

Reputation:

This can be accomplished with an IF statement and an ISBLANK statement. The formula below follows this sequence: 1. Evaluate the conditional: Is the cell in column B blank? 2a. If it IS BLANK, put the next parameter (e.g. a blank string "") 2b. If it IS NOT BLANK, put the third parameter (e.g. the TEXT function which formats the column.

=IF(ISBLANK(B:B),"",TEXT(B:B, "dddd"))

You got very close! Don't forget the IF statement syntax:

=IF(logical_statement, [value_if_true], [value_if_false])

Let me know if this works for you!

EDIT: Your method works a little differently and omits the ISBLANK statement. This is fine albeit less readable. The error you ran into is actually just the quotes surrounding the TEXT function. If you want a string of text to appear as-is, then put it in quotes. If you want the text to evaluate as a formula, leave the quotes out.

EDIT: A more bullet-proof formula follows, which will only display the day of the week if the cell in column M is a date of the correct format. This formula will display an empty string if it is unable to detect a date. @paulbica

=IF(ISERROR(DATEVALUE(TEXT(M:M,"mm/dd/yyyy"))),"",TEXT(M:M, "dddd"))

Upvotes: 2

Related Questions