![]() The Min Function is to avoid the situation that the last day in the last week falls in the next year, and December 31 will be the last day in that case. Adding the weeks to get the last day of the week in the week number. The logic is to get the last day of the last full week before the week with January 1, and then to add days in weeks to the week number.įor example, the last day of the last full week is Decemcalculated by the formula: To get the last day of the week given the week number, please use the formula below and change YYYY and WK# to the values you have: The Max Function is to avoid the first day in the first week falls in the year before (e.g., December 27, 2015). To get the first day of any week in 2016, please use the formula below. If the week number is 2, for example, you need to use the very first day to add 7 days which is (2-1)*7, or if you know the week number is 3, you need to use the very first day to add (3-1)*7 days to have the first day of the 3rd week. If you want Monday as the first day of the week, please use 2 instead of 1. Where 1 in the weekday function is the weekday type, please see below for the full list. Weeks are according Malaysia calendar rules, Monday first day and weeks are Monday to Sunday. To get "December 27, 2015" based on January 1, please use the following formula: The very first day of the week which has Januis December 27, 2015. ![]() =MAX(DATE(YYYY,1,1), DATE(YYYY,1,1) – WEEKDAY(DATE(YYYY,1,1), 1) + 1 + (WK#-1)*7)įor example, the first day Januis the 6th day of the week if you use Sunday the first day of the week. Please copy the formula below, change "YYYY" and "WK#" to the year and the week number to get the first day of the week. Since there are always 7 days in a week and to calculate the first day of the week based on the week number, you will need to figure out the very first day in the first week of the year, then to add the days to the week number. On the other hand, you can also calculate the days in the week if you know the week number in the year. You can easily convert a date into the week number in Excel with the WeekNum Function. All weeks are starting on Monday and ending on Sunday.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |