![]() You can earn a commission for sales leads that you send to us by joining our Any affiliate commissions that weĮarn when you click a link to Amazon or other sites is reinvested in keeping Website are provided "as is" and we do not guarantee that they can be used in all The Formulas, Functions and Visual Basic procedures on this provides examples of Formulas, Functions and Visual Basic proceduresįor illustration only, without warranty either expressed or implied, includingīut not limited to the implied warranties of merchantability and/or fitness forĪ particular purpose. Something like this : 27 (Weeknumber entered in cell A1 by user) 2001 (Year entered in cell A2 by user) J(Date for Monday in cell A4 generated by Excel) J(Date for Tuesday in cell A5 generated by Excel) J(Date for Wednesday in cell A6 generated by Excel) J(Date for Thursday in cell A7 generated by Excel) J(Date for Friday in cell A8 generated by Excel) Could anyone please tell me if this is possible ? Thanks ! :-) Frits Jager Hello, Could someone please help me with the following little problem ? Is it possible to calculate date's from a weeknummer and a year entered by a user ? When a user enters this I would Excel like to generate the dates for Monday to Friday. MyWeekD = myDay & "/" & myMonth & "/" & myYear Private Sub Worksheet_Change(ByVal Target As Excel.Range) Just one thing, though, it misses the first week in January 2001 bacause the first day is a Monday - but you can play around If you then enter a week number in A1 and Year in A2, the dates will appear. Try using this code in the Sheet3 (right click on the sheet tab and select Hope that helps Hello, Could someone please help me with the following little problem ? Is it possible to calculate date's from a weeknummer and a year entered by a user ? When a user enters this I would Excel like to generate the dates for Monday to Friday. This formula always returns the Monday of the week in question (you can check it with the WEEKDAY function), so in a5 you can put the formula ",ĭocumentation.Author = " Imke Feldmann ",ĭocumentation.And format the cell to a date format you like. This system is the methodology specified in ISO 8601, which is commonly known as the European week numbering system.Įquivalent of the YEARFRAC-Function in Excel. System 2 The week containing the first Thursday of the year is the first week of the year, and is numbered as week 1. System 1 The week containing January 1 is the first week of the year, and is numbered week 1. There are two systems used for this function: For example, the week containing January 1 is the first week of the year, and is numbered week 1. ",ĭocumentation.LongDescription = " Returns the week number of a specific date. Equivalent of the YEARFRAC-Function in Excel. Result = if Return_type = 21 then IsoWeek else Defaultĭocumentation.Description = " Returns the week number of a specific date. ![]() LastWeekOfThisYear = getNaiveWeek(#date(thisYear, 12, 28)),ĭefault = Date.WeekOfYear(Date, ConvertedNumber), LastWeekOfPriorYear = getNaiveWeek(#date(priorYear, 12, 28)), NaiveWeek = Number.RoundDown((ordinal – weekday + 10) / 7) ![]() ![]() WeekdayOfJan4th = getDayOfWeek(#date(Date.Year(inDate), 1, 4)), Result = 1 + Date.DayOfWeek(d, Day.Monday) IsoWeek = // this function comes from r-k-b on Github: PQ native Date.WeekFromYear starts to count from 0(Sunday) to 6(Saturday) as opposed to Excel from 1(Sunday) to 7(Saturday) For a detailled description about the options of the Return_types see the official documentation: (Date as date, optional Return_type as number) => ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |