=text(a1,"dddd") question

[Dylan @ UAFC]

I am trying to auto fill or copy down this
formula, when I copy down Saturday shows up in every
cell, even if the orginal cell blank

How can I re-work the function so the cell will remain
blank until data is typed in the other cell

 

[Mike H]

Hi

=IF(A1<>"",TEXT(A1,"dddd"),"")

Mke

 

[Dylan @ UAFC]

would that work on this formula as well
=A1-WEEKDAY(A1)+7

 

[Mike H]

yup

=IF(A1="","",A1-WEEKDAY(A1)+1)

Mike

would that work on this formula as well
=A1-WEEKDAY(A1)+7

 

[Gord Dibben]

I would guess your Calculation mode is set to Manual.

Mike addressed the second part.


Gord Dibben MS Excel MVP

 

[Mike H]

Gord,

I've never understood why but blank cells always evaluate as saturday even
if you use

=TEXT(WEEKDAY(A1),"dddd")

so the calculation manual/auto may not come into it.

Mike

Blank cell

 

[T. Valko]

I've never understood why but blank cells always evaluate as saturday

Because Excel recognizes a day 0. Think of the date serial numbers. 1 is Jan
1 1900 which Excel says is a Sunday.

1/0/1900 is a valid date in Excel and Excel evaluates day 0 as the last day
of the prior month so 0 or an empty cell represents 12/31/1899 which Excel
evaluates as Saturday even though 12/31/1899 is not a valid date.

Basically it's one of Excel's many quirks!

 

[Mike H]

I should have been able to work that out myself but didn't. Thanks for the info

Mike

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

I should have been able to work that out myself but didn't. Thanks for the
info

Mike

 

[Gord Dibben]

Mike.

I missed the "even if blank cell".

Biff

Thanks for setting us straight on the Saturday thing.


Gord

 

[T. Valko]

My pleasure!

--
Biff
Microsoft Excel MVP

Mike.

I missed the "even if blank cell".

Biff

Thanks for setting us straight on the Saturday thing.


Gord