|
Return the ISO Standards Start of the Year in Excel - First Monday of the Year - UDF
Return the date of the first Monday of any year with this UDF ISO Year function in Excel. This is a really simple and easy to use UDF (user defined function) in Excel. You just input the desired year, in 4 digit format (1996, 2001, etc.) and the function will return the date of the first Monday in that year.
Make sure the cell where the function is located is formatted as a date because the result of the function is a date serial number.
Where to install the macro: Module
UDF to Return the ISO Standards Start of the Year in Excel - First Monday of the Year
Function ISOYEARSTART(Year As Integer) As Date
Dim WeekDay As Integer
Dim NewYear As Date
NewYear = DateSerial(Year, 1, 1)
WeekDay = (NewYear - 2) Mod 7
If WeekDay < 4 Then
ISOYEARSTART = NewYear - WeekDay
Else
ISOYEARSTART = NewYear - WeekDay + 7
End If
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 am trying to do year to date figures, with a comparison year on year.
I have revenue under current year and the previous 2 years revenue as well. I have reduced down the spreadsheet attached just to keep things simple.
How do I get my total colums to add up year to date numbers depended on the month rather than the whole lot or using lots of = + + + , tried using =sum (index) but that adds them all together.
Hope someone know what I am talking about and will save me having to ammend formula's every month!! This is part of a bigger job with the same style page over many pages!!
Thanks
Pete
I am trying to write a function that will display the first Monday of the year based on the user entry.
For example, if I enter;
5/1/2011, It should produce 1/3/2011...
12/31/2012, it should produce 1/2/2012...
etc...
I am sure that this is very easy, however I am having a tough time with this one. Thanks in advance.
I would like to put the year in A1...
A2-A6 would populate the Dates for Monday - Friday
A7-A11 Monday - Friday
A12-16 Monday - Friday...
etc, etc for the year 52 weeks....Help please...
I'm creating a new report and want to include a formula that will automatically determine the date of the first Monday in a new year. Column A is populated with every day of the year in mm/dd/yy format. I want to put the formula into cell B1 and use the same date format. I tried a VLOOKUP, but had no luck. I think there is probably some other way of getting what I need, but I'm not sure how to do it. Any ideas?
Hello,
I have a list of dates starting from the first date of the current year 1/1/10 to the end of the year 31/12/10 in the A column. I input the first day of the year manually and the rest of the dates are filled in with a +1 formula.
Is there a formula that can pick up the 1st monday of the year? eg 4/1/10 would be for THIS year
(Please try to refrain from using NOW or TODAY formulas that will change the date as soon as the new year comes along- i change the first date of the year when i want )
Cheers!
Thanks everyone on the board for your help in advance.
I have a list of monthly data going back several years, like this :
A B
1/31/06 96
2/28/06 101
......
12/31/08 105
In columnC, I would like to calculate a year-over-year year-to-date percentage change, essentially by summing columnB year-to-date for any given year (i.e. 2007), and put this total over last year's year-to-date (i.e. 2006).
In any given year, I have used the absolute reference feature ($) to anchor the sum calculation, and this works for a 12 month period (ex: =SUM(B$28:D33)/SUM(B$16:D21)-1). That is, I anchor the sum function to January of each year. But it also requires a manual update to the absolute reference for every calendar year change. Since my data goes back decades, I would like to find a way to automatically draw the formula down the whole column.
TIA
Normally when you specify a month and a day in Excel but omit the year,
the current year is assumed. I have a peculiar problem with one of our
computers defaulting to the year 2001, five years ago. For example, I
type "June 1" and Excel stores "01/06/2001" instead of the expected
"01/06/2006".
Note that both computers have the correct system date and are set to
interpret 2-digit years as being between 1930 and 2029. Both computers
are running Windows XP Home Edition 2002 Service Pack 2 and Microsoft
Office 2000.
I know the simple answer would be to type out the year in full but I am
curious as to why this would be happening. A quick search on the
internet shows that Excel's expected behaviour when a month and a
number are typed, is to store the month, day and current year (if the
number is 31 or lower). Has anyone else experienced this?
This file was created and is saved on a desktop computer (which
interprets the year correctly) and is being accessed by a laptop (which
interprets the wrong year) over a network.
Thanks,
Shannon
Hi,
I am trying to create a 100% stacked chart in Excel 2003.
The data is as below:
Current Year Previous Year
Month Comp1 Comp2 Comp3 Comp1 Comp2 Comp3
APR09 100 150 175 275 175 200
MAY09 250 235 185 350 193 220
JUN09 275 259 204 450 212 242
JUL09 303 284 224 425 233 266
I have managed to create a 100% stacked chart but only for 1 set of period. I havent been able to add the previous year to it.
I need the previous year as stacked as well but in a separate column.
I hope I have added all the required information.
This is my first question to a forum ever, so please be gentle!
Many thanks
Honey
Dear All
My finacial year starts on the 1st of October of each year and ends 30th of September each year.
My week is from Monday to Saturday - though for calculation ease: lets say that my week is from Monday to Sunday.
I need to calculate the week numbers of a date based on the above two criteria.
I understand that I need to ascertain the day of the start date first i.e: did 01/10/07 fall on Monday, Tuesday etc. - In fact it was Monday!
so for my year 07/08:
Week 1 was 01/10/07 to 07/10/07
Week 2 was 08/10/07 to 14/10/07
ETC
So in the attached worksheet - what formula can I use to populate column D - by using the data in column E?
I.e. All I want to do, is input date into E and B, C, D will be calculated automatically. It would be great of excel could populate A - aswell ;-)
All help will be much appreciated.
I have a column of months (A) and a column of wages (B) corresponding to the start month of new recruits and their wage.
How do I sum up the total for the year ?
Example:
Start date:Jan in Column A, Wage 100 in Column B
Start date:Oct in Column A, Wage 150 in Column B
That would equal a total wage cost in the year of (100*12)+(150*3)=1650
I cant seem to work out a formula. My months come from a dropdown list, wages are whatever the user types into the cell next to the dropdown list of month.
Is that clear, or should i give up for Friday ?
|
|
