If and a date

[Box666]

I have a formula that gives me the first day of the week that a
transaction took place.

=K2-WEEKDAY(K2)+2

This works fine so long as there is a date in "K2" but if it is blank
then i get a #Value. error
I have tried using various "if" statements to say if K2 is blank then
the destination cell should also be blank. But all of my "if"
statements keep giving errors. (K2 is formatted as dd-mmm-yyyy if that
makes a difference.)

What is the best way to write this please.

Bob

 

[Kevin B]

Try the following:

=IF(ISBLANK(K2),"",K2-WEEKDAY(K2)+2)

Or this, which covers just about everything:

IF(ISERROR(K2-WEEKDAY(K2)+2),"",K2-WEEKDAY(K2)+2)

 

[Box666]

Try the following:

=IF(ISBLANK(K2),"",K2-WEEKDAY(K2)+2)

Or this, which covers just about everything:

IF(ISERROR(K2-WEEKDAY(K2)+2),"",K2-WEEKDAY(K2)+2)
--
Kevin Backmann







- Show quoted text -

Thanks, option 2 sorted it.

Bob

 

[Gord Dibben]

If K2 was truly blank, your formula would return -5

You have something in K2 in order to produce the #VALUE! error.

A space perhaps or "" created from a formula in K2?


Gord Dibben MS Excel MVP