How can I create formula that turns a date into the week # in the.

D

Don

Hi,
I just started this new job. I have to create a report that converts the
date into the week # for the year. 1/1/04 would be 1, etc.
The formula is set up in a former employee's document, but when I copy and
paste, it doesn't transfer correctly...I just get #NAME? in the field.
The formula currently written for the cells is =WEEKNUM(D2,1).
I am pretty bad when it comes to formulas. Can any one HELP. I would greatly
appreciate it. I want to leave for Thanksgiving Holiday :)
Thank you,
Don
 
D

don

Don't know if I made sense when I typed this...
1/1/04 would be 1 for the 1st week in the year.
2/10/04 would be given value 6 for the 6th week of the year, etc.
thanks
 
J

JE McGimpsey

XL stores dates as integer offsets from a base date (31 December 1899 by
default in Windows XL), so you can just do regular math on them:

A1: <date>
A2: =A1-DATE(YEAR(A1),1,0)

Format A2 as General

Using 0 as the day argument will cause Date to return the last day of
the previous month, therefore if you enter 23 November 2004, the formula
will return 329, and if you enter 1 January 2005, the formula will
return 1,
 
B

Brandt

Don,

You have to have the Analysis ToolPak loaded for that function to work. That
is why you are getting the #NAME? error - because excel doesn't recognise
this formula (yet). To load this click on Tools in the menu bar then click
 
J

JE McGimpsey

WEEKNUM will work if you have the Analysis Toolpak Add-in installed
(Tools/Add-ins... check the appropriate checkbox).
 
M

Myrna Larson

Did you check Help for this function? It explains the reason for the error
your see, and what to do about it.
 
D

don

THANK YOU!!!!!!!!!!!!!. this did the trick.

JE McGimpsey said:
WEEKNUM will work if you have the Analysis Toolpak Add-in installed
(Tools/Add-ins... check the appropriate checkbox).
Click to expand...
 
Top