If you want to return the week number for the standard ISO calendar, try using the ISOWEEKNUM function. Sub addyear () Dim currentdate As Date currentdate DateAdd ('yyyy', 4, '') MsgBox Format (currentdate, 'dd-mm-yyyy') End Sub The result will be as below. Now the date "" falls in week 51 instead of week 52, as seen in the previous example.
You can follow the question or vote as helpful, but you cannot reply to this thread. Create, view, edit, and share your spreadsheets using Excel for Mac. With Microsoft 365, you get features as soon as they are released ensuring you’re always working with the latest. How do I change it to where the final outcome is This thread is locked. Microsoft 365 includes premium Word, Excel, and PowerPoint apps, 1 TB cloud storage in OneDrive, advanced security, and more, all in one convenient subscription. A number format in text form in the Category box on the Number tab in the Format Cells dialog box. If you pass in a parameter of 21 as firstdayofweek, it will renumber the weeks so that the week starts on Monday and the first Thursday of the year is the first week of the year. Excel 2010 - Date changes to the 1900s instead of 2000s When I am typing in the date example- 5/29/35 enter and it changes it to. A numeric value, a formula that evaluates to a numeric value, or a reference to a cell containing a numeric value. You can change how the weeks in a year are numbered with the firstdayofweek parameter. In cell C2, enter A2+B2, and copy down as needed. You can enter a negative number to subtract days from your start date, and a positive number to add to your date.
Most often you will want a formatted date rather than a serial date.Ĭolumn B shows the date from column A as an unformatted serial date.īased on the Excel spreadsheet above, the following WEEKNUM examples would return: =WEEKNUM(A2) Enter the number of days to add or subtract in column B. The syntax of the DateAdd Function is: DateAdd(Interval, Number, Date) where: Interval A string that specifies the type of interval to use.
Let's look at some Excel WEEKNUM function examples and explore how to use the WEEKNUM function as a worksheet function in Microsoft Excel:Ĭolumn A displays the serial date parameter for the WEEKNUM function formatted using mmm d, yyyy. You can use the DateAdd Function to add a date/time interval to a date or time, and the function will return the resulting date/time.