|
Converting Dates to Weeks
Hey All,
I require a worksheet function or group of function that will allow me to convert my dates of format dd/mm/yyyy to Week 1, Week 2, Week 3, Week 4. Hope someone can help. Kurt |
Converting Dates to Weeks
have a look in the help index for WEEKNUM
-- Don Guillett SalesAid Software "Kurt" wrote in message ... Hey All, I require a worksheet function or group of function that will allow me to convert my dates of format dd/mm/yyyy to Week 1, Week 2, Week 3, Week 4. Hope someone can help. Kurt |
Converting Dates to Weeks
Look at WEEKNUM function.
"Kurt" wrote: Hey All, I require a worksheet function or group of function that will allow me to convert my dates of format dd/mm/yyyy to Week 1, Week 2, Week 3, Week 4. Hope someone can help. Kurt |
Converting Dates to Weeks
Decide what you consider your start date to be Heres what i did enter a start date in A1 (01/07/2006) in B1 enter =A1 then format it as a number (38899) so to turn that into week numbers I just retyped into B1 =INT((A2-38899)/7)+1 -- Special-K ------------------------------------------------------------------------ Special-K's Profile: http://www.excelforum.com/member.php...fo&userid=7470 View this thread: http://www.excelforum.com/showthread...hreadid=563275 |
Converting Dates to Weeks
Oh, but that formula exists and it is called WEEK. Write your date in A1 and be sure to have the format so that is interpreted as a date. If you want to be sure just write 38871 which is the value for 20-07-2006 Then try this in B1: ="Week "&WEEK(A1) Just one important thing: Americans regard week 1 to be the remaining of the week containing January 1'st, so week 1 is normalkly shorter than 7 days. In Europe week 1 is the first week in a year if January 1'st is Thursday or earlier; else it is called week 53. This makes the counts differ in America and the rest of the world and Excel uses the american way. -- nsv ------------------------------------------------------------------------ nsv's Profile: http://www.excelforum.com/member.php...o&userid=26500 View this thread: http://www.excelforum.com/showthread...hreadid=563275 |
Converting Dates to Weeks
Each of the suggestions worked. What if I wanted to limit to the week number
for a particular month. For example if 1/1/06 will be in week1 in Jan-06 and 28/1/06 will be week4 in Jan-06? How can I achieve this. Kurt "nsv" wrote: Oh, but that formula exists and it is called WEEK. Write your date in A1 and be sure to have the format so that is interpreted as a date. If you want to be sure just write 38871 which is the value for 20-07-2006 Then try this in B1: ="Week "&WEEK(A1) Just one important thing: Americans regard week 1 to be the remaining of the week containing January 1'st, so week 1 is normalkly shorter than 7 days. In Europe week 1 is the first week in a year if January 1'st is Thursday or earlier; else it is called week 53. This makes the counts differ in America and the rest of the world and Excel uses the american way. -- nsv ------------------------------------------------------------------------ nsv's Profile: http://www.excelforum.com/member.php...o&userid=26500 View this thread: http://www.excelforum.com/showthread...hreadid=563275 |
Converting Dates to Weeks
Do you mean that each month will start at week 1?
"Kurt" wrote: Each of the suggestions worked. What if I wanted to limit to the week number for a particular month. For example if 1/1/06 will be in week1 in Jan-06 and 28/1/06 will be week4 in Jan-06? How can I achieve this. Kurt "nsv" wrote: Oh, but that formula exists and it is called WEEK. Write your date in A1 and be sure to have the format so that is interpreted as a date. If you want to be sure just write 38871 which is the value for 20-07-2006 Then try this in B1: ="Week "&WEEK(A1) Just one important thing: Americans regard week 1 to be the remaining of the week containing January 1'st, so week 1 is normalkly shorter than 7 days. In Europe week 1 is the first week in a year if January 1'st is Thursday or earlier; else it is called week 53. This makes the counts differ in America and the rest of the world and Excel uses the american way. -- nsv ------------------------------------------------------------------------ nsv's Profile: http://www.excelforum.com/member.php...o&userid=26500 View this thread: http://www.excelforum.com/showthread...hreadid=563275 |
Converting Dates to Weeks
Yes. each month to start at week 1.
Kurt "Toppers" wrote: Do you mean that each month will start at week 1? "Kurt" wrote: Each of the suggestions worked. What if I wanted to limit to the week number for a particular month. For example if 1/1/06 will be in week1 in Jan-06 and 28/1/06 will be week4 in Jan-06? How can I achieve this. Kurt "nsv" wrote: Oh, but that formula exists and it is called WEEK. Write your date in A1 and be sure to have the format so that is interpreted as a date. If you want to be sure just write 38871 which is the value for 20-07-2006 Then try this in B1: ="Week "&WEEK(A1) Just one important thing: Americans regard week 1 to be the remaining of the week containing January 1'st, so week 1 is normalkly shorter than 7 days. In Europe week 1 is the first week in a year if January 1'st is Thursday or earlier; else it is called week 53. This makes the counts differ in America and the rest of the world and Excel uses the american way. -- nsv ------------------------------------------------------------------------ nsv's Profile: http://www.excelforum.com/member.php...o&userid=26500 View this thread: http://www.excelforum.com/showthread...hreadid=563275 |
Converting Dates to Weeks
"Kurt" wrote in message
... "nsv" wrote: Oh, but that formula exists and it is called WEEK. Write your date in A1 and be sure to have the format so that is interpreted as a date. If you want to be sure just write 38871 which is the value for 20-07-2006 Then try this in B1: ="Week "&WEEK(A1) Just one important thing: Americans regard week 1 to be the remaining of the week containing January 1'st, so week 1 is normalkly shorter than 7 days. In Europe week 1 is the first week in a year if January 1'st is Thursday or earlier; else it is called week 53. This makes the counts differ in America and the rest of the world and Excel uses the american way. Each of the suggestions worked. What if I wanted to limit to the week number for a particular month. For example if 1/1/06 will be in week1 in Jan-06 and 28/1/06 will be week4 in Jan-06? How can I achieve this. I don't know where nsv finds his WEEK() function, as it doesn't seem to be an Excel function, but for your revised question, try =WEEKNUM(A1)-WEEKNUM(DATE(YEAR(A1),MONTH(A1),1))+1 -- David Biddulph |
Converting Dates to Weeks
It is WEEKNUM allright, not WEEK - I was wrong there. My version of Excel is in my national language (very annoying, but they will not give me an english version), so I cannot always check the syntax NSV -- nsv ------------------------------------------------------------------------ nsv's Profile: http://www.excelforum.com/member.php...o&userid=26500 View this thread: http://www.excelforum.com/showthread...hreadid=563275 |
| All times are GMT +1. The time now is 07:05 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com