If statement
 

I need help with a formula/macro. I have a formula that selects cell c9 and
subtracts it by 1 day. If that day is a Sunday, I want the formula to
subtract by 3 days. However, my current formula doesn't subtract by 3.
Please help

C9 : May 21, 2007

C12 =IF(C9-1= "Sunday",C9-3,C9-1)

If statement
 

Hi David,
How about C12=IF(WEEKDAY(C9-1)=7,C9-3,C9-1)

--
Ken Hudson


"David T" wrote:

I need help with a formula/macro. I have a formula that selects cell c9 and
subtracts it by 1 day. If that day is a Sunday, I want the formula to
subtract by 3 days. However, my current formula doesn't subtract by 3.
Please help

C9 : May 21, 2007

C12 =IF(C9-1= "Sunday",C9-3,C9-1)

If statement
 

Here's one way:

=IF(TEXT(C9-1,"ddd")="Sun",C9-3.C9-1)

Biff

"David T" wrote in message
...
I need help with a formula/macro. I have a formula that selects cell c9
and
subtracts it by 1 day. If that day is a Sunday, I want the formula to
subtract by 3 days. However, my current formula doesn't subtract by 3.
Please help

C9 : May 21, 2007

C12 =IF(C9-1= "Sunday",C9-3,C9-1)

If statement
 

One way:

C12: =C9-1-2*(WEEKDAY(C9)=1)

WORKDAY() is in the Analysis Toolpak Add-in (Tools/Add-ins...)


In article ,
David T wrote:

I need help with a formula/macro. I have a formula that selects cell c9 and
subtracts it by 1 day. If that day is a Sunday, I want the formula to
subtract by 3 days. However, my current formula doesn't subtract by 3.
Please help

C9 : May 21, 2007

C12 =IF(C9-1= "Sunday",C9-3,C9-1)

If statement
 

Typo:

=IF(TEXT(C9-1,"ddd")="Sun",C9-3.C9-1)

Should be:

=IF(TEXT(C9-1,"ddd")="Sun",C9-3,C9-1)

Format as DATE

Biff

"T. Valko" wrote in message
...
Here's one way:

=IF(TEXT(C9-1,"ddd")="Sun",C9-3.C9-1)

Biff

"David T" wrote in message
...
I need help with a formula/macro. I have a formula that selects cell c9
and
subtracts it by 1 day. If that day is a Sunday, I want the formula to
subtract by 3 days. However, my current formula doesn't subtract by 3.
Please help

C9 : May 21, 2007

C12 =IF(C9-1= "Sunday",C9-3,C9-1)

If statement
 

=IF(WEEKDAY(C9-1)=7,C9-3,C9-1)

Using that syntax weekday 7 = Saturday

Try:

=IF(WEEKDAY(C9-1)=1,C9-3,C9-1)
=IF(WEEKDAY(C9-1,2)=7,C9-3,C9-1)

Biff

"Ken Hudson" wrote in message
...
Hi David,
How about C12=IF(WEEKDAY(C9-1)=7,C9-3,C9-1)

--
Ken Hudson


"David T" wrote:

I need help with a formula/macro. I have a formula that selects cell c9
and
subtracts it by 1 day. If that day is a Sunday, I want the formula to
subtract by 3 days. However, my current formula doesn't subtract by 3.
Please help

C9 : May 21, 2007

C12 =IF(C9-1= "Sunday",C9-3,C9-1)

If statement
 

Probably not a great concern, but if your workbook may be used with
non-English versions of XL, using WEEKDAY(C9)=1 will still work where
TEXT(C9-1,"ddd")="Sun" won't.

In article ,
"T. Valko" wrote:

Typo:

=IF(TEXT(C9-1,"ddd")="Sun",C9-3.C9-1)

Should be:

=IF(TEXT(C9-1,"ddd")="Sun",C9-3,C9-1)

Format as DATE

Biff

"T. Valko" wrote in message
...
Here's one way:

=IF(TEXT(C9-1,"ddd")="Sun",C9-3.C9-1)

Biff

"David T" wrote in message
...
I need help with a formula/macro. I have a formula that selects cell c9
and
subtracts it by 1 day. If that day is a Sunday, I want the formula to
subtract by 3 days. However, my current formula doesn't subtract by 3.
Please help

C9 : May 21, 2007

C12 =IF(C9-1= "Sunday",C9-3,C9-1)

If statement
 

Ignore the third line - got pasted from a previous message...

In article ,
JE McGimpsey wrote:

One way:

C12: =C9-1-2*(WEEKDAY(C9)=1)

WORKDAY() is in the Analysis Toolpak Add-in (Tools/Add-ins...)


In article ,
David T wrote:

I need help with a formula/macro. I have a formula that selects cell c9
and
subtracts it by 1 day. If that day is a Sunday, I want the formula to
subtract by 3 days. However, my current formula doesn't subtract by 3.
Please help

C9 : May 21, 2007

C12 =IF(C9-1= "Sunday",C9-3,C9-1)

If statement
 

Hello everyone-

Thanks for your help. All of those formulas work great. I only picked one
and added it to my sheet.

"JE McGimpsey" wrote:

One way:

C12: =C9-1-2*(WEEKDAY(C9)=1)

WORKDAY() is in the Analysis Toolpak Add-in (Tools/Add-ins...)


In article ,
David T wrote:

I need help with a formula/macro. I have a formula that selects cell c9 and
subtracts it by 1 day. If that day is a Sunday, I want the formula to
subtract by 3 days. However, my current formula doesn't subtract by 3.
Please help

C9 : May 21, 2007

C12 =IF(C9-1= "Sunday",C9-3,C9-1)

If statement
 

And... I misinterpreted "that day":

=C9-1-2*(WEEKDAY(C9)=2)


In article ,
JE McGimpsey wrote:

One way:

C12: =C9-1-2*(WEEKDAY(C9)=1)

WORKDAY() is in the Analysis Toolpak Add-in (Tools/Add-ins...)


In article ,
David T wrote:

I need help with a formula/macro. I have a formula that selects cell c9
and
subtracts it by 1 day. If that day is a Sunday, I want the formula to
subtract by 3 days. However, my current formula doesn't subtract by 3.
Please help

C9 : May 21, 2007

C12 =IF(C9-1= "Sunday",C9-3,C9-1)

If statement
 

WORKDAY() is in the Analysis Toolpak Add-in (Tools/Add-ins...)

Yeah, WORKDAY is, but not WEEKDAY.

Think you need to subtract 1 in WEEKDAY:

=C9-1-2*(WEEKDAY(C9-1)=1)

Biff

"JE McGimpsey" wrote in message
...
One way:

C12: =C9-1-2*(WEEKDAY(C9)=1)

WORKDAY() is in the Analysis Toolpak Add-in (Tools/Add-ins...)


In article ,
David T wrote:

I need help with a formula/macro. I have a formula that selects cell c9
and
subtracts it by 1 day. If that day is a Sunday, I want the formula to
subtract by 3 days. However, my current formula doesn't subtract by 3.
Please help

C9 : May 21, 2007

C12 =IF(C9-1= "Sunday",C9-3,C9-1)

If statement
 

Yup, see my two (or is it three) previous posts...

Thanks for the correction.

In article ,
"T. Valko" wrote:

WORKDAY() is in the Analysis Toolpak Add-in (Tools/Add-ins...)

Yeah, WORKDAY is, but not WEEKDAY.

Think you need to subtract 1 in WEEKDAY:

=C9-1-2*(WEEKDAY(C9-1)=1)

Biff

"JE McGimpsey" wrote in message
...
One way:

C12: =C9-1-2*(WEEKDAY(C9)=1)

WORKDAY() is in the Analysis Toolpak Add-in (Tools/Add-ins...)


In article ,
David T wrote:

I need help with a formula/macro. I have a formula that selects cell c9
and
subtracts it by 1 day. If that day is a Sunday, I want the formula to
subtract by 3 days. However, my current formula doesn't subtract by 3.
Please help

C9 : May 21, 2007

C12 =IF(C9-1= "Sunday",C9-3,C9-1)