#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 78
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9,101
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 78
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Adding a yearly calendar to excel spreadsheet Sharon Excel Worksheet Functions 2 January 11th 08 05:31 PM
Yearly total average? Tammy Excel Discussion (Misc queries) 8 March 17th 06 03:30 AM
automate calendar dates on worksheets to make a yearly planner Tom Excel Discussion (Misc queries) 0 March 2nd 06 05:25 PM
Update yearly records ED Excel Discussion (Misc queries) 1 January 19th 06 11:08 PM
yearly invoice Rave Excel Worksheet Functions 0 June 7th 05 03:42 PM


All times are GMT +1. The time now is 01:18 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"