| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I convert a date to a week of the year?
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I convert a date to a week of the year?
use weeknum() function
HTH, -- Pranav Vaidya VBA Developer PN, MH-India If you think my answer is useful, please rate this post as an ANSWER!! "JBTexas" wrote: 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I convert a date to a week of the year?
I'm trying to do that but I'm gettng #REF... and I'm not able to trace the
problem. "Pranav Vaidya" wrote: use weeknum() function HTH, -- Pranav Vaidya VBA Developer PN, MH-India If you think my answer is useful, please rate this post as an ANSWER!! "JBTexas" wrote: 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I convert a date to a week of the year?
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 "JBTexas" wrote: I'm trying to do that but I'm gettng #REF... and I'm not able to trace the problem. "Pranav Vaidya" wrote: use weeknum() function HTH, -- Pranav Vaidya VBA Developer PN, MH-India If you think my answer is useful, please rate this post as an ANSWER!! "JBTexas" wrote: 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I convert a date to a week of the year?
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. -- Kevin Backmann "JBTexas" wrote: I'm trying to do that but I'm gettng #REF... and I'm not able to trace the problem. "Pranav Vaidya" wrote: use weeknum() function HTH, -- Pranav Vaidya VBA Developer PN, MH-India If you think my answer is useful, please rate this post as an ANSWER!! "JBTexas" wrote: 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I convert a date to a week of the year?
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 "bj" wrote: 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 "JBTexas" wrote: I'm trying to do that but I'm gettng #REF... and I'm not able to trace the problem. "Pranav Vaidya" wrote: use weeknum() function HTH, -- Pranav Vaidya VBA Developer PN, MH-India If you think my answer is useful, please rate this post as an ANSWER!! "JBTexas" wrote: 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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I convert a date to a week of the year?
I confirmed that I have the add-in checked - pressed F9 and still nothing...
"Kevin B" wrote: 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. -- Kevin Backmann "JBTexas" wrote: I'm trying to do that but I'm gettng #REF... and I'm not able to trace the problem. "Pranav Vaidya" wrote: use weeknum() function HTH, -- Pranav Vaidya VBA Developer PN, MH-India If you think my answer is useful, please rate this post as an ANSWER!! "JBTexas" wrote: 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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I convert a date to a week of the year?
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. "JBTexas" wrote: 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 "bj" wrote: 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 "JBTexas" wrote: I'm trying to do that but I'm gettng #REF... and I'm not able to trace the problem. "Pranav Vaidya" wrote: use weeknum() function HTH, -- Pranav Vaidya VBA Developer PN, MH-India If you think my answer is useful, please rate this post as an ANSWER!! "JBTexas" wrote: 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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I convert a date to a week of the year?
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) -- Kevin Backmann "JBTexas" wrote: I confirmed that I have the add-in checked - pressed F9 and still nothing... "Kevin B" wrote: 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. -- Kevin Backmann "JBTexas" wrote: I'm trying to do that but I'm gettng #REF... and I'm not able to trace the problem. "Pranav Vaidya" wrote: use weeknum() function HTH, -- Pranav Vaidya VBA Developer PN, MH-India If you think my answer is useful, please rate this post as an ANSWER!! "JBTexas" wrote: 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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I convert a date to a week of the year?
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 B" wrote: 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) -- Kevin Backmann "JBTexas" wrote: I confirmed that I have the add-in checked - pressed F9 and still nothing... "Kevin B" wrote: 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. -- Kevin Backmann "JBTexas" wrote: I'm trying to do that but I'm gettng #REF... and I'm not able to trace the problem. "Pranav Vaidya" wrote: use weeknum() function HTH, -- Pranav Vaidya VBA Developer PN, MH-India If you think my answer is useful, please rate this post as an ANSWER!! "JBTexas" wrote: 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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I convert a date to a week of the year?
Kevin - I tried what you suggested by entering the Date...etc. for one cell -
that still gives me #REF. "JBTexas" wrote: 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 B" wrote: 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) -- Kevin Backmann "JBTexas" wrote: I confirmed that I have the add-in checked - pressed F9 and still nothing... "Kevin B" wrote: 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. -- Kevin Backmann "JBTexas" wrote: I'm trying to do that but I'm gettng #REF... and I'm not able to trace the problem. "Pranav Vaidya" wrote: use weeknum() function HTH, -- Pranav Vaidya VBA Developer PN, MH-India If you think my answer is useful, please rate this post as an ANSWER!! "JBTexas" wrote: 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 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I convert a date to a week of the year?
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... -- Kevin Backmann "JBTexas" wrote: Kevin - I tried what you suggested by entering the Date...etc. for one cell - that still gives me #REF. "JBTexas" wrote: 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 B" wrote: 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) -- Kevin Backmann "JBTexas" wrote: I confirmed that I have the add-in checked - pressed F9 and still nothing... "Kevin B" wrote: 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. -- Kevin Backmann "JBTexas" wrote: I'm trying to do that but I'm gettng #REF... and I'm not able to trace the problem. "Pranav Vaidya" wrote: use weeknum() function HTH, -- Pranav Vaidya VBA Developer PN, MH-India If you think my answer is useful, please rate this post as an ANSWER!! "JBTexas" wrote: 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 |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I convert a date to a week of the year?
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. "Kevin B" wrote: 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... -- Kevin Backmann "JBTexas" wrote: Kevin - I tried what you suggested by entering the Date...etc. for one cell - that still gives me #REF. "JBTexas" wrote: 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 B" wrote: 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) -- Kevin Backmann "JBTexas" wrote: I confirmed that I have the add-in checked - pressed F9 and still nothing... "Kevin B" wrote: 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. -- Kevin Backmann "JBTexas" wrote: I'm trying to do that but I'm gettng #REF... and I'm not able to trace the problem. "Pranav Vaidya" wrote: use weeknum() function HTH, -- Pranav Vaidya VBA Developer PN, MH-India If you think my answer is useful, please rate this post as an ANSWER!! "JBTexas" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Finding the date using the number of the week in a year | Excel Worksheet Functions | |||
| how to insert month date year and day of week | Excel Discussion (Misc queries) | |||
| how to insert month date year and day of week | New Users to Excel | |||
| how to insert month date year and day of week | Excel Worksheet Functions | |||
| Format an excel column as a date for a 5 day week for a year. | Excel Discussion (Misc queries) | |||
Linear Mode
