If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Help with Dates
I need help with a date function.
I want to be able to put a date in one cell and have another cell tell me what number week it falls in. For example if I were to input 2/12 in cell A1 then the cell next to it would tell me that 2/12 is the 6th week of the year. I've looked through the date functions and can find similar functions but nothing that will return the number of the week. As each date is actually stored as a sequential number based off of 1/1/1900 I am sure there is a way to figure out the number of the week. If anyone out there can help I would appreciated it. Thanks Steve |
#2
|
|||
|
|||
Help with Dates
Hi Steve,
Different places start the 1st week differently. So if you don't get the results you want check out Chip Pearson's site. 2004-02-12 for that date I get 7 with both =Datenum(a25,1) and with =datenum(a25,2) so I expect you are not in the US. so you will have to see Chip's site: from his topic.htm to see WEEKNUM and ISO Week Numbers http://www.cpearson.com/excel/weeknum.htm -- --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "SMonczka" wrote in message om... I need help with a date function. I want to be able to put a date in one cell and have another cell tell me what number week it falls in. For example if I were to input 2/12 in cell A1 then the cell next to it would tell me that 2/12 is the 6th week of the year. I've looked through the date functions and can find similar functions but nothing that will return the number of the week. As each date is actually stored as a sequential number based off of 1/1/1900 I am sure there is a way to figure out the number of the week. If anyone out there can help I would appreciated it. Thanks Steve |
#3
|
|||
|
|||
Help with Dates
If you have the Analysis Toolpak (ATP)* installed and enabled,
perhaps try using WEEKNUM ? For example, supposing you have dates in col A, you could pickup the week # in col B by putting: In B1: =weeknum(A1,2) and copying down col B The 2nd arg in WEEKNUM, e.g.: "2" in B1 above, is a number that determines on what day the week begins. "2" means the week begins on Monday. The default is "1" - week begins on Sunday. -- *Check the "Analysis Toolpak" box via Tools Add-Ins Chip Pearson's page has details on the ATP at: http://www.cpearson.com/excel/ATP.htm -- Rgds Max xl 97 --- Please respond, in newsgroup xdemechanik atyahoodotcom ---- "SMonczka" wrote in message om... I need help with a date function. I want to be able to put a date in one cell and have another cell tell me what number week it falls in. For example if I were to input 2/12 in cell A1 then the cell next to it would tell me that 2/12 is the 6th week of the year. I've looked through the date functions and can find similar functions but nothing that will return the number of the week. As each date is actually stored as a sequential number based off of 1/1/1900 I am sure there is a way to figure out the number of the week. If anyone out there can help I would appreciated it. Thanks Steve |
Thread Tools | |
Display Modes | |
|
|