How do I convert a date to a week of the year?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've got a column of dates that I'd like to have converted to the week # of
the year, I'm not so concerned with the date in that week as I am which weeks
are represented in the column
 
I can't make weeknum() give a #ref error
a workaround is to use datedif
=int(datedif(date(2007,1,1),now())/7)
you need to manipulate this to tell when you actually want week 1 to start
 
Click TOOLS in the menu and select ADD INS. Verify that you have the
ANALYSIS TOOLPACK checked.

After turning on the add in you can press F9 to recalc the workbook and your
#REF error should be replaced by a week number.
 
this is what I've entered:
=weeknum(G42, 1)
note: G42 = 2/7/2007

I'm not sure how to utilize your workaround either... how do I use datedif
 
the equation should have been
=int(datedif(date(2007,1,1),now(),"d")/7)

datedif() is an undocumented function in Excel
if you google it you will see a lot of comments on it.
 
If your date has been entered as a string you could get wonky results from
the WEEKNUM formula. Try entering your date value using the DATE function to
get a true date:

DATE(Year,Month,Day)

=DATE(2007,9,5)
 
I actually have a column with 900+ rows of dates that I wanted to the week
number for...is it possible to get to this without re-entering?
 
Kevin - I tried what you suggested by entering the Date...etc. for one cell -
that still gives me #REF.
 
I've run out of ideas at this point. If possible see if this happens on a
different computer. In my experience the #REF error means that the formula
cannot be found, and in this case the week number formula is part of the
Analysis Toolpack.

Hope you can get this resolved...
 
I ended up copying and pasting just the values - that changed my date in to
all numbers (# of days + 1900) and that seems to work! Strange. But, okay.
Thank you all of your help.
 
Back
Top