Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
yearly calendar
Hi
some year ago i received a very useful spreadsheet: In columns you had weeks (w/1, w/2 etc..) and in the row above you had a little arrow with a formula that made the arrow move from one week to the following one. can you help me out ? cheers -- Carla |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
yearly calendar
you may want to check the Microsoft website. There are 159 different
calendars for 2008. http://office.microsoft.com/en-us/te...425391033.aspx "Carla" wrote: Hi some year ago i received a very useful spreadsheet: In columns you had weeks (w/1, w/2 etc..) and in the row above you had a little arrow with a formula that made the arrow move from one week to the following one. can you help me out ? cheers -- Carla |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
yearly calendar
Thanks for that, but I wanted something much more straightforward.
Say for example column A column B column C row 2 row 3 week 14/08 week 15/08 week 16/08 row 4 31/03 - 05/04 07/04 - 11/04 17/04 - 21/04 If we are April 7th, I wanted to appear in row2-column B a downwards arrow pointing at week 15/08. Or, is there a way to make "week 15/08" bold/highlighted ? thanks carla -- Carla "Joel" wrote: you may want to check the Microsoft website. There are 159 different calendars for 2008. http://office.microsoft.com/en-us/te...425391033.aspx "Carla" wrote: Hi some year ago i received a very useful spreadsheet: In columns you had weeks (w/1, w/2 etc..) and in the row above you had a little arrow with a formula that made the arrow move from one week to the following one. can you help me out ? cheers -- Carla |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
yearly calendar
For both of the following, I'm assuming that the word "week" (or "Week")
followed by a single space precedes your weeknumber/year as shown in your sample. If that is not the actual case, you will have to adjust the MID(A3,6,5) section in both of the formulas offered below. 1) To create your moving "arrow", select all of Row 2 and then select Format/Cells from Excel's menu bar. On the dialog box that appears, click the Alignment tab and select Center from the Horizontal drop down under the Text Alignment section; then click the Font tab and select Marlett for the Font, Bold from the Font Style list and 12 from the Size list. Click the OK button when done. Now, put this formula in A2... =IF(MID(A3,6,5)=WEEKNUM(TODAY())&TEXT(TODAY(),"\/yy"),"u","") and copy it across. Is that what you are looking for? 2) Just to give you the choice, you can also make the "Week 15/08" text Bold as well as highlight the cell containing it with a color. Select all of Row 3 and click Format/Conditional Formatting on Excel's menu bar. Select "Formula Is" from the first drop down and put this formula in the next field over... =MID(A3,6,5)=(1+INT((TODAY()-(DATE(YEAR(TODAY()),1,2)-WEEKDAY(DATE(YEAR(TODAY()),1,1))))/7))&TEXT(TODAY(),"\/yy") Now, click the Format button and click on the Font tab. Select Bold from the Font Style list. Next, click the Patterns tab and pick a light (pale) color from the color chart. Finally, OK your way back to the worksheet. Rick "Carla" wrote in message ... Thanks for that, but I wanted something much more straightforward. Say for example column A column B column C row 2 row 3 week 14/08 week 15/08 week 16/08 row 4 31/03 - 05/04 07/04 - 11/04 17/04 - 21/04 If we are April 7th, I wanted to appear in row2-column B a downwards arrow pointing at week 15/08. Or, is there a way to make "week 15/08" bold/highlighted ? thanks carla -- Carla "Joel" wrote: you may want to check the Microsoft website. There are 159 different calendars for 2008. http://office.microsoft.com/en-us/te...425391033.aspx "Carla" wrote: Hi some year ago i received a very useful spreadsheet: In columns you had weeks (w/1, w/2 etc..) and in the row above you had a little arrow with a formula that made the arrow move from one week to the following one. can you help me out ? cheers -- Carla |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
yearly calendar
=MID(A3,6,5)=(1+INT((TODAY()-(DATE(YEAR(TODAY()),1,2)-WEEKDAY(DATE(YEAR(TODAY()),1,1))))/7))&TEXT(TODAY(),"\/yy")
I forgot to credit the author of the Week Number code used in part of the above Conditional Formatting formula. The "The Excel Analysis ToolPak function WEEKNUM" write-up on this page... http://www.rondebruin.nl/weeknumber.htm credits a Daniel M (too bad we don't have his full name) with developing the formula equivalent of the Analysis ToolPak's (ATP) WEEKNUM formula (which my formula assumes Carla used in constructing her Row 3 headers). It was necessary to use Daniel M's formula because the Conditional Formatting dialog wouldn't let a formula reference the ATP function directly. Rick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding a yearly calendar to excel spreadsheet | Excel Worksheet Functions | |||
Yearly total average? | Excel Discussion (Misc queries) | |||
automate calendar dates on worksheets to make a yearly planner | Excel Discussion (Misc queries) | |||
Update yearly records | Excel Discussion (Misc queries) | |||
yearly invoice | Excel Worksheet Functions |