If and a date
 

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

If and a date
 

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


"Box666" wrote:

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

If and a date
 

On 24 Jan, 18:12, Kevin B wrote:
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



"Box666" wrote:
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 adatein "K2" butifit is blank
then i get a #Value. error
I have tried using various "if" statements to sayifK2 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-yyyyifthat
makes a difference.)

What is the best way to write this please.

Bob- Hide quoted text -

- Show quoted text -

Thanks, option 2 sorted it.

Bob

If and a date
 

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

On Thu, 24 Jan 2008 10:06:44 -0800 (PST), Box666
wrote:

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