| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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) |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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) |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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) |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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) |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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) |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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) |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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) |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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) |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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) |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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) |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| appending and IF statement to an existing IF statement | Excel Worksheet Functions | |||
| Help with IF Statement | Excel Worksheet Functions | |||
| IF Statement Help please | Excel Worksheet Functions | |||
| If statement and Isblank statement | Excel Worksheet Functions | |||
| Help please, IF statement/SUMIF statement | Excel Worksheet Functions | |||
Linear Mode
