Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Format row using date
Help!!! I think this can be done but Im not sure how to do it I need to
highlight an entire row basted on a date in the first column. I will explain. Column A has dates starting with 7/6/09 I have information in columns B-AE if the date in A falls into this week (changing each week) A-AE will highlight in orange Can this be done as a conditional format or a VBA? Any help would be great. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Format row using date
Hi,
Select the entire row and apply the folowing conditional format formula =AND(WEEKNUM(A1)=WEEKNUM(NOW()),YEAR(A1)=YEAR(NOW( ))) Mike "Carolina Girl" wrote: Help!!! I think this can be done but Im not sure how to do it I need to highlight an entire row basted on a date in the first column. I will explain. Column A has dates starting with 7/6/09 I have information in columns B-AE if the date in A falls into this week (changing each week) A-AE will highlight in orange Can this be done as a conditional format or a VBA? Any help would be great. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Format row using date
Mike
Thanks for the fast response. But my understanding is that WEEKNUM will not work in 2003. Do you happen to have another suggestion??? I should also mention that my list of dates are weeks ie 7/6, 7/13,7/20. If today is 7/15 I need Cells A-E highlighted under for week of 7/13. Do you have any other suggestions? Thanks again "Mike H" wrote: Hi, Select the entire row and apply the folowing conditional format formula =AND(WEEKNUM(A1)=WEEKNUM(NOW()),YEAR(A1)=YEAR(NOW( ))) Mike "Carolina Girl" wrote: Help!!! I think this can be done but Im not sure how to do it I need to highlight an entire row basted on a date in the first column. I will explain. Column A has dates starting with 7/6/09 I have information in columns B-AE if the date in A falls into this week (changing each week) A-AE will highlight in orange Can this be done as a conditional format or a VBA? Any help would be great. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Format row using date
If WEEKNUM does not work in your version of XL2003 it is because you
need to install the Analysis tool-pack. Check out XL Help on WEEKNUM for further details. However, I don't think it will work if your data is in the form 7/6, 7/13 etc. I don't understand this format - can you elaborate? Pete On Sep 18, 2:43*pm, Carolina Girl wrote: Mike Thanks for the fast response. But my understanding is that WEEKNUM will not work in 2003. Do you happen to have another suggestion??? I should also mention that my list of dates are weeks ie 7/6, 7/13,7/20. If today is 7/15 I need Cells A-E highlighted under for week of 7/13. Do you have any other suggestions? Thanks again "Mike H" wrote: Hi, Select the entire row and apply the folowing conditional format formula =AND(WEEKNUM(A1)=WEEKNUM(NOW()),YEAR(A1)=YEAR(NOW( ))) Mike "Carolina Girl" wrote: Help!!! I think this can be done but Im not sure how to do it I need to highlight an entire row basted on a date in the first column. I will explain. Column A has dates starting with 7/6/09 I have information in columns B-AE if the date in A falls into this week (changing each week) A-AE will highlight in orange Can this be done as a conditional format or a VBA? *Any help would be great.- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Format row using date
Thanks Pete... My only question would be is if someone else opens this
spreadsheet and has not installed the Analysis tool-pack would they be able to see the conditional format formula work. The reason I ask is I am building this for another manager at my office. As for the 7/6 date I just have it formatted with the Format cells - date- 3/14 should this be formatted in a different way? Thanks again for all the help "Pete_UK" wrote: If WEEKNUM does not work in your version of XL2003 it is because you need to install the Analysis tool-pack. Check out XL Help on WEEKNUM for further details. However, I don't think it will work if your data is in the form 7/6, 7/13 etc. I don't understand this format - can you elaborate? Pete On Sep 18, 2:43 pm, Carolina Girl wrote: Mike Thanks for the fast response. But my understanding is that WEEKNUM will not work in 2003. Do you happen to have another suggestion??? I should also mention that my list of dates are weeks ie 7/6, 7/13,7/20. If today is 7/15 I need Cells A-E highlighted under for week of 7/13. Do you have any other suggestions? Thanks again "Mike H" wrote: Hi, Select the entire row and apply the folowing conditional format formula =AND(WEEKNUM(A1)=WEEKNUM(NOW()),YEAR(A1)=YEAR(NOW( ))) Mike "Carolina Girl" wrote: Help!!! I think this can be done but Im not sure how to do it I need to highlight an entire row basted on a date in the first column. I will explain. Column A has dates starting with 7/6/09 I have information in columns B-AE if the date in A falls into this week (changing each week) A-AE will highlight in orange Can this be done as a conditional format or a VBA? Any help would be great.- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Format row using date
Ah, right !! So 7/6 means 6th July, 7/13 is 13th July ? And you are
not showing the year ? Yes, you are right in that if the file is used on a PC where the ATP is not installed then that user will get the errors you mentioned earlier. I usually avoid using ATP functions and other add-ins for this very reason, as you can't ensure that users will have the ATP installed. Possible ways around this: a. include a UDF which gives you the week number (disadvantages: your users will have to enable macros when they start with the file, and if they don't then they will see errors), b. use a different formula which calculates week number by using only the standard Excel functions. Hope this helps. Pete On Sep 18, 8:27*pm, Carolina Girl wrote: Thanks Pete... My only question would be is if someone else opens this spreadsheet and has not installed the Analysis tool-pack would they be able to see the conditional format formula work. The reason I ask is I am building this for another manager at my office. As for the 7/6 date I just have it formatted with the Format cells - date- 3/14 should this be formatted in a different way? Thanks again for all the help "Pete_UK" wrote: If WEEKNUM does not work in your version of XL2003 it is because you need to install the Analysis tool-pack. Check out XL Help on WEEKNUM for further details. However, I don't think it will work if your data is in the form 7/6, 7/13 etc. I don't understand this format - can you elaborate? Pete On Sep 18, 2:43 pm, Carolina Girl wrote: Mike Thanks for the fast response. But my understanding is that WEEKNUM will not work in 2003. Do you happen to have another suggestion??? I should also mention that my list of dates are weeks ie 7/6, 7/13,7/20. If today is 7/15 I need Cells A-E highlighted under for week of 7/13. Do you have any other suggestions? Thanks again "Mike H" wrote: Hi, Select the entire row and apply the folowing conditional format formula =AND(WEEKNUM(A1)=WEEKNUM(NOW()),YEAR(A1)=YEAR(NOW( ))) Mike "Carolina Girl" wrote: Help!!! I think this can be done but Im not sure how to do it I need to highlight an entire row basted on a date in the first column. I will explain. Column A has dates starting with 7/6/09 I have information in columns B-AE if the date in A falls into this week (changing each week) A-AE will highlight in orange Can this be done as a conditional format or a VBA? *Any help would be great.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
permanent conversion of 1904 date format to 1900 date format | Excel Worksheet Functions | |||
Convert european foreign date format to US date format | Excel Worksheet Functions | |||
convert serial date format to normal date format | Excel Discussion (Misc queries) | |||
Convert date + time text format to date format | Excel Worksheet Functions | |||
Excel 2000 date format cannot be set to Australian date format | Excel Discussion (Misc queries) |