|
Return the ISO Week Number from a Date in Excel - UDF
Return the ISO Week Number in Excel with this UDF. This is a simple to use UDF (user defined function) that returns the number of the week in the year that a specific date occurs.
Where to install the macro: Module
UDF to Return the ISO Week Number from a Date in Excel
Function ISOWEEKNUMBER(InDate As Date) As Long
Dim D As Date
D = DateSerial(Year(InDate - WeekDay(InDate - 1) + 4), 1, 3)
ISOWEEKNUMBER = Int((InDate - D + WeekDay(D) + 5) / 7)
End Function
How to Install the Macro
- Select and copy the text from within the grey box above.
- Open the Microsoft Excel file in which you would like the Macro to function.
- Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.
Or For other ways to get there, Click Here.
For Excel Versions Prior to Excel 2007
Go to Tools > Macros > Visual Basic Editor
For Excel 2007
Go to Office Button > Excel Options > Popular > Click Show Developer tab in the Ribbon. Then go to the Developer tab on the ribbon menu and on the far left Click Visual Basic
- On the new window that opens up, go to the left side where the vertical pane is located. Locate your Excel file; it will be called VBAProject (YOUR FILE'S NAME HERE) and click this.
- If the Macro goes in a Module, Click Here, otherwise continue to Step 8.
- Go to the menu at the top of the window and click Insert > Module
- Another window should have opened within the Visual Basic Editor's window. Within this new window, paste the macro code. Make sure to paste the code underneath the last line of anything else that is in the window.
- Go to Step 8.
- If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.
- Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
- Then, at the bottom of the list that appears, double-click the ThisWorkbook text.
- A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
- Go to Step 8.
- If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.
- Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
- Within the list that appears you will see every worksheet that is in that excel file. They will be listed as such: Sheet1(NAME OF SHEET HERE) and under that will be Sheet2(NAME OF SHEET HERE). Select the sheet in which you want the macro to run and double-click that sheet.
- A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
- Repeat steps b and c for every sheet you want the macro to work in. Putting the macro in one sheet will not enable it for any other sheets in the workbook.
- Go to Step 8.
- Close the Microsoft Visual Basic Editor window and save the Excel file. When you close the Visual Basic Editor window, the regular Excel window will not close.
- You are now ready to run the macro.
Similar Helpful Excel Resources
Hi
I have a spreadie that I'm trying to reduce human input into. One of the inputs that I use is to enter to current week number (1 to 52).
I would like to automate this entry by using a calculation that returns which week number it is. I have a table on one of the tabs in which the first column contains weekending dates and the second column has a corresponding week number - these match my employers financial year rather than calendar year.
My formula would ideally be - "if today's date is x, then search column A for the week ending in which x occurs and return the week number from column b"
The answer would return to the same cell each week as I have other formulas that use the figure it returns.
Any help is much appreciated.
L.
Is there a formula i can use to return the work week for a date - starting at week 1 for the first week of january and so on?
hi guys
Can anyone please help me to find the week start date(monday) and weekend(sunday) using the week number. I am able to find the weeknumber using the code below but don't know how to find the start date and end date of the week.
Code:
Dim iNumberOfTheWeek As Integer
iNumberOfTheWeek = DatePart("ww", Now())
Regards
Aman
I need to get the week number in excell from a cell with a date (dd/mm/aaaa),
but the first week of the year has 3 possiblilities, first week, with day
nº 1, first week with at least 4 days of the new year or finally the first
full week.
How to do this in excell ? I tried with weeknum() but I can't choose the
first week
Thanks.
1. In the source data, insert a new column and insert the WEEKNUM function into it.
2. In the second argument of the Function Arguments dialog box, set Return_type to 2 (1 = Sunday, 2 = Monday).
3. Refresh the PivotTable, and drag the Week Number new filed into Values area.
Hi room
On sheet 1, I have three columns (A,B & C) that contain job number, baseline & target dates respectively. On another sheet, sheet 2, I have two columns - one containing week numbers for 2008 (1-52) - in the second column I'd like to look up the baseline date in sheet one and return the task number in the cell adjacent to the respective 2008 week number.
I think I need employ INDEX and MATCH but cannot workout the required criteria.
Any help offered really would be gratefully appreciated.
the formula for getting the week number(no analysis toolpak) I'm using is:
=1+INT((B4-(DATE(YEAR(B4),1,2)-WEEKDAY(DATE(YEAR(B4),1,1))))/7)
I am just trying to figure a way to convert that week number into the actual start date of that specific week its calculating. For example, the start date of week 52 in 2007 is 12/23/2007. And the start date of week 52 in 2006 is 12/24/2006.
I'd like to do this without creating tables for all of 2004,2005,2006, etc.
Thanks so much, in advance.
Hi there all, Im fairly new but I am learning quickly
Just wonder if anyone could give me a pointer.
I am using my own week1 date, and I am trying to identify the weeks which may have a certain date of every month back, so I can use it as criteria to return a value. For example if weeks 3, 7, and 12 all had the 12th in them, it would return 1 in the cell beside the week.
Does that make sense? My technical talk is not very good!
Any help I could get here would be greatly appreciated!
Cheers
*EDIT* I have attached a sheet describing what im looking to do. Thanks AAE!
Also, I would like to do this in VBA. Cheers
I'm wanting to know how to set up a formula/which formula to use so that if I put in a week number, it will display the relevant targets for that week.
eg if i put week 2 into colum a2, I want specific targets relating to that week number displayed in colums c2, d2, e2 and so on.
I am trying to set up a date field in a spreadsheet that will always give the
date of the upcoming Saturday. I have tried the "Weekday" function along with
the "If" and the "If/Or" statements without success. Can someone help?
|
|
