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

[Guest]

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

 

[Guest]

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

 

[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,

 

[JE McGimpsey]

Disregard - I completely misread the original post.

 

[Guest]

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

 

[JE McGimpsey]

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

 

[JE McGimpsey]

See also

http://cpearson.com/excel/weeknum.htm

 

[Myrna Larson]

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

 

[Guest]

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

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