Need Excel Formula to convert formula to value

Lairdrice · 09-13-2008, 06:04 AM

In Col A I have the date, formatted as yy-mm-dd
In Col B I have a formula: "=A1", formatted as ddd

That means that Col B displays the "day of the week" as text

There are other columns with stuff in them...

In Col F, I want to do a formula ONLY IF that row is not a Sat or Sun.

=If(OR(A1<>"Sat",A1<>"Sun"),"Formula goes here","Sorry its a weekend")

Of course, that formula doesn't work because Col B is a formula, not the text.

Without creating a new column that has a static PasteValues of the displayed text in Col B, is this possible?

Thanks!

David M58 · 09-13-2008, 12:20 PM

Here's one way to do it. You can use a formula in column F such as:

=IF(AND(TEXT(A1,"ddd")<>"Sat",TEXT(A1,"ddd")<>"Sun"),"Formula goes here","Sorry its a weekend")

**macropod** · 09-14-2008, 05:51 PM

Hi Lairdrice,

Try:
=IF(MOD(A1,7)>1,"Formula goes here","Sorry its a weekend")
or:
=IF(MOD(B1,7)>1,"Formula goes here","Sorry its a weekend")

09-13-2008, 06:04 AM	#1 (permalink)
Lairdrice Registered User Join Date: Sep 2008 Posts: 1 OS: XP	Need Excel Formula to convert formula to value In Col A I have the date, formatted as yy-mm-dd In Col B I have a formula: "=A1", formatted as ddd That means that Col B displays the "day of the week" as text There are other columns with stuff in them... In Col F, I want to do a formula ONLY IF that row is not a Sat or Sun. =If(OR(A1<>"Sat",A1<>"Sun"),"Formula goes here","Sorry its a weekend") Of course, that formula doesn't work because Col B is a formula, not the text. Without creating a new column that has a static PasteValues of the displayed text in Col B, is this possible? Thanks!

09-13-2008, 12:20 PM	#2 (permalink)
David M58 Registered User Join Date: May 2008 Location: Baltimore, Maryland Posts: 160 OS: Windows XP SP3	Re: Need Excel Formula to convert formula to value Here's one way to do it. You can use a formula in column F such as: =IF(AND(TEXT(A1,"ddd")<>"Sat",TEXT(A1,"ddd")<>"Sun"),"Formula goes here","Sorry its a weekend") Last edited by David M58; 09-13-2008 at 12:25 PM.

09-14-2008, 05:51 PM	#3 (permalink)
macropod TSF Enthusiast Join Date: Apr 2008 Location: Australia Posts: 562 OS: Vista	Re: Need Excel Formula to convert formula to value Hi Lairdrice, Try: =IF(MOD(A1,7)>1,"Formula goes here","Sorry its a weekend") or: =IF(MOD(B1,7)>1,"Formula goes here","Sorry its a weekend") __________________ Cheers macropod (MS MVP -Word)