| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Weeknum vs Conditional Formatting
Excel2003 ... Weeknum vs Conditional Formatting???
Cell E1 ... =IF(ISBLANK($D1),"",D1+7) ... Return = 01/14/07 ... ok Cell E3 ... Conditional Format Formula ... Format Pattern Gold ... not working??? =weeknum(today(),2)<weeknum(e$1,2) ... Returns error message = "You may not use references to other worksheets or workbooks for Conditional Formatting criteria." I am stuck ... Help! ... Thanks ... Kha |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Weeknum vs Conditional Formatting
The Weeknum function is part of the Analysis Toolpak, an add-in, which Excel
sees as another workbook. Try this instead =1+INT((TODAY()-(DATE(YEAR(TODAY()),1,2)-WEEKDAY(DATE(YEAR(TODAY()),1,1))))/7)<1+INT((TODAY()-(DATE(YEAR($E$1),1,2)-WEEKDAY(DATE(YEAR($E$1),1,1))))/7) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ken" wrote in message ... Excel2003 ... Weeknum vs Conditional Formatting??? Cell E1 ... =IF(ISBLANK($D1),"",D1+7) ... Return = 01/14/07 ... ok Cell E3 ... Conditional Format Formula ... Format Pattern Gold ... not working??? =weeknum(today(),2)<weeknum(e$1,2) ... Returns error message = "You may not use references to other worksheets or workbooks for Conditional Formatting criteria." I am stuck ... Help! ... Thanks ... Kha |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Weeknum vs Conditional Formatting
=weeknum(today(),2)
You could give that portion a defined name like, say, WeekToday Then: =WeekToday<1+INT((TODAY()-(DATE(YEAR($E$1),1,2)-WEEKDAY(DATE(YEAR($E$1),1,1))))/7) Biff "Bob Phillips" wrote in message ... The Weeknum function is part of the Analysis Toolpak, an add-in, which Excel sees as another workbook. Try this instead =1+INT((TODAY()-(DATE(YEAR(TODAY()),1,2)-WEEKDAY(DATE(YEAR(TODAY()),1,1))))/7)<1+INT((TODAY()-(DATE(YEAR($E$1),1,2)-WEEKDAY(DATE(YEAR($E$1),1,1))))/7) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ken" wrote in message ... Excel2003 ... Weeknum vs Conditional Formatting??? Cell E1 ... =IF(ISBLANK($D1),"",D1+7) ... Return = 01/14/07 ... ok Cell E3 ... Conditional Format Formula ... Format Pattern Gold ... not working??? =weeknum(today(),2)<weeknum(e$1,2) ... Returns error message = "You may not use references to other worksheets or workbooks for Conditional Formatting criteria." I am stuck ... Help! ... Thanks ... Kha |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Weeknum vs Conditional Formatting
I think the second half of the formula is mixed up. You need to replace
TODAY() with E1. With that change some of the week numbers are off by 1 compared to Excel's WEEKNUM function: =WEEKNUM(date,2) Your formula seems to be based on return_type 1 (Sunday is first DOW) In my other reply I assumed that was correct and just copied that portion into my formula. With this portion given the defined name of WeekToday: =WEEKNUM(TODAY(),2) This corrected formula should work: =WeekToday<INT((WEEKDAY(DATE(YEAR(E1),1,2-2))+E1-DATE(YEAR(E1),1,-5))/7) From Mike Holland posted on Dick's Daily Dose of Excel: Replacement for the ATP WEEKNUM function: =INT((WEEKDAY(DATE(YEAR(date),1,2-return_type))+date-DATE(YEAR(date),1,-5))/7) Biff "Bob Phillips" wrote in message ... The Weeknum function is part of the Analysis Toolpak, an add-in, which Excel sees as another workbook. Try this instead =1+INT((TODAY()-(DATE(YEAR(TODAY()),1,2)-WEEKDAY(DATE(YEAR(TODAY()),1,1))))/7)<1+INT((TODAY()-(DATE(YEAR($E$1),1,2)-WEEKDAY(DATE(YEAR($E$1),1,1))))/7) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ken" wrote in message ... Excel2003 ... Weeknum vs Conditional Formatting??? Cell E1 ... =IF(ISBLANK($D1),"",D1+7) ... Return = 01/14/07 ... ok Cell E3 ... Conditional Format Formula ... Format Pattern Gold ... not working??? =weeknum(today(),2)<weeknum(e$1,2) ... Returns error message = "You may not use references to other worksheets or workbooks for Conditional Formatting criteria." I am stuck ... Help! ... Thanks ... Kha |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Weeknum vs Conditional Formatting
The replacement for the ATP Weeknum function worked (nice to know) ... That
said ... this turned out much more complicated than I anticipated ... but "thanks" to you & others that continue to support these boards ... I am there .... Thanks ... Kha "T. Valko" wrote: I think the second half of the formula is mixed up. You need to replace TODAY() with E1. With that change some of the week numbers are off by 1 compared to Excel's WEEKNUM function: =WEEKNUM(date,2) Your formula seems to be based on return_type 1 (Sunday is first DOW) In my other reply I assumed that was correct and just copied that portion into my formula. With this portion given the defined name of WeekToday: =WEEKNUM(TODAY(),2) This corrected formula should work: =WeekToday<INT((WEEKDAY(DATE(YEAR(E1),1,2-2))+E1-DATE(YEAR(E1),1,-5))/7) From Mike Holland posted on Dick's Daily Dose of Excel: Replacement for the ATP WEEKNUM function: =INT((WEEKDAY(DATE(YEAR(date),1,2-return_type))+date-DATE(YEAR(date),1,-5))/7) Biff "Bob Phillips" wrote in message ... The Weeknum function is part of the Analysis Toolpak, an add-in, which Excel sees as another workbook. Try this instead =1+INT((TODAY()-(DATE(YEAR(TODAY()),1,2)-WEEKDAY(DATE(YEAR(TODAY()),1,1))))/7)<1+INT((TODAY()-(DATE(YEAR($E$1),1,2)-WEEKDAY(DATE(YEAR($E$1),1,1))))/7) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ken" wrote in message ... Excel2003 ... Weeknum vs Conditional Formatting??? Cell E1 ... =IF(ISBLANK($D1),"",D1+7) ... Return = 01/14/07 ... ok Cell E3 ... Conditional Format Formula ... Format Pattern Gold ... not working??? =weeknum(today(),2)<weeknum(e$1,2) ... Returns error message = "You may not use references to other worksheets or workbooks for Conditional Formatting criteria." I am stuck ... Help! ... Thanks ... Kha |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Weeknum vs Conditional Formatting
You're welcome. Thanks for the feedback!
Biff "Ken" wrote in message ... The replacement for the ATP Weeknum function worked (nice to know) ... That said ... this turned out much more complicated than I anticipated ... but "thanks" to you & others that continue to support these boards ... I am there ... Thanks ... Kha "T. Valko" wrote: I think the second half of the formula is mixed up. You need to replace TODAY() with E1. With that change some of the week numbers are off by 1 compared to Excel's WEEKNUM function: =WEEKNUM(date,2) Your formula seems to be based on return_type 1 (Sunday is first DOW) In my other reply I assumed that was correct and just copied that portion into my formula. With this portion given the defined name of WeekToday: =WEEKNUM(TODAY(),2) This corrected formula should work: =WeekToday<INT((WEEKDAY(DATE(YEAR(E1),1,2-2))+E1-DATE(YEAR(E1),1,-5))/7) From Mike Holland posted on Dick's Daily Dose of Excel: Replacement for the ATP WEEKNUM function: =INT((WEEKDAY(DATE(YEAR(date),1,2-return_type))+date-DATE(YEAR(date),1,-5))/7) Biff "Bob Phillips" wrote in message ... The Weeknum function is part of the Analysis Toolpak, an add-in, which Excel sees as another workbook. Try this instead =1+INT((TODAY()-(DATE(YEAR(TODAY()),1,2)-WEEKDAY(DATE(YEAR(TODAY()),1,1))))/7)<1+INT((TODAY()-(DATE(YEAR($E$1),1,2)-WEEKDAY(DATE(YEAR($E$1),1,1))))/7) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ken" wrote in message ... Excel2003 ... Weeknum vs Conditional Formatting??? Cell E1 ... =IF(ISBLANK($D1),"",D1+7) ... Return = 01/14/07 ... ok Cell E3 ... Conditional Format Formula ... Format Pattern Gold ... not working??? =weeknum(today(),2)<weeknum(e$1,2) ... Returns error message = "You may not use references to other worksheets or workbooks for Conditional Formatting criteria." I am stuck ... Help! ... Thanks ... Kha |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional Formatting using Formulas and WEEKNUM | Excel Discussion (Misc queries) | |||
| conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
| conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
| weeknum formatting | Excel Discussion (Misc queries) | |||
| Conditional Formatting that will display conditional data | Excel Worksheet Functions | |||
Linear Mode
