And here is the result returned by the formula: Of course, the formula to convert a week number to a date is not trivial, and it may take a while to get your head round the logic. =MIN(DATE(A2+1,1,0), DATE(A2,1,1) - WEEKDAY(DATE(A2,1,1),2) + B2*7), To calculate the start and end dates for a Sunday - Saturday week, all it takes is one small adjustment in the above formulas :), Start date: Thank you! rev2023.5.1.43405. . Mar 23, 2022 at 8:27. Thank you. Note: The above formula will give your converted date in text format, so you will see the results aligned to the left side of the cells. I already have a weekly project schedule created so I know what needs designed for which event. Im not going to discuss either of those approachesbecause my preferred approach is to use the TEXT() function. July 1 = week 1 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: =MIN (DATE (YYYY,12,31), DATE (YYYY,1,1) -WEEKDAY (DATE (YYYY,1,1), 1) + 7*WK#) The logic is to get the last day of the last full week before the week with January 1, and then to add days . =DATE(A2,1,-2) << I just checked this, it receives the last tuesday of the previous year, and not the last monday. WEEKDAY(serial_number,[return_type]) The WEEKDAY function syntax has the following arguments: Serial_number Required. Please check out the following article on our blog, itll be sure to help you with your task: Calculate difference between two dates in days, weeks A date that represents the start date. Do you have a method do go by calendar? Alternatively, you could add 6 directly in the formula: =DATE(A2, 1, -2) - WEEKDAY(DATE(A2, 1, 3)) + B2 * 7 + 6. Could you please help me out, answering through my e-mail or through this page? The ISO 8601 definition for week 01 is the week with the first Thursday of the Gregorian year (i.e. Thank you very much for this post, very usefulc clear and detailed. This video demonstrates how to use Excel's WEEKNUM and ISOWEEKNUM functions to convert a date into its week number.-----All enrolments and. Hello, 12/28/2020 2020 53 12/28/2020 1/3/2021 Here, we will show you how to use ISOWEEKNUM, DATE, YEAR, and MONTH functions to convert date to week number of the month. 4 (weeks date gen.auto(sept 1st 2016) With 10-01-2016 In all other respects, these formulas work similarly to Formula 1 above. . =D2+6. =WEEKDAY (B2) If you'd like to use the date directly in the function, type the following WEEKDAY function with your date in it and press Enter. Hi! Ablebits is a fantastic product - easy to use and so efficient, I don't know how to thank you enough for your Excel add-ins. Adjacent to your chosen arbitrary date, create a vlookup formula. Next, type in the date for the week on which you want to begin (for instance, if your first day of data is 5/1/13, and you your weeks start on Sunday, then you'd type in 4/28/13). I just realized my mistake -- I'm deriving the year from the date, rather than from the weekly period, which would be 2020-53, not 2021-53, for 1/1/2021 through 1/3/2021. And thanks in Advance again. Share Improve this answer Follow edited Apr 6, 2016 at 3:40 sampathsris 21.3k 11 69 98 answered May 24, 2014 at 19:55 Richard Kernick 1 On the other hand, using the same formula we got 2 as the week number for 5th September 2021. Note. I don't want to mess the whole thing up by adding new columns and messing the whole thing up!! I want WEEKNUM(plannedDate) to return the week number, but I am getting some sort of error. Since the rule applies to A2:B15, it highlights values in both columns. Thank you for the method. Since the formula still refers to the Sunday of the first day of this week (January 5th), It returns as week number 2. Thank you! 15-11-2022 21-11-2022, Also the leap month of this year 2023 creates havoc Could you please solve my issue. : How to Convert Date to Week Number of Month in Excel (5 Ways), 5 Ways to Convert Date to Week Number of Month in Excel, 1. =WEEKNUM ("3/15/2017") Result: 11 For explanation of the vlookup formula, see https://www.ablebits.com/office-addins-blog/2014/07/17/excel-vlookup-tutorial-beginner/. Learn more about Stack Overflow the company, and our products. What's the cheapest way to buy out a sibling's share of our parents house if I have no cash and want to pay less than the appraised value? If there is, please describe. If your business logic requires converting a specific date to the week number within the corresponding month, you can use the combination of WEEKNUM, DATE and MONTH functions: Assuming that cell A2 contains the original date, use the following formula for a week beginning on Monday (notice 21 in WEEKNUM's return_type argument): =WEEKNUM($A2,21)-WEEKNUM(DATE(YEAR($A2), MONTH($A2),1),21)+1. If Serial_number is out of range for the current date base value, a #NUM! Hi,I want to do a week num formula like this, but want to avoid non-working days in my formula.. any ideas on how I can do this? 12/31/2020 2020 53 12/28/2020 1/3/2021 The WORKDAY function syntax has the following arguments: Start_date Required. Start date: You get the last day of week with same formula adding 6. 10-11-2022 14-11-2022 First, we used the WEEKDAY function to find the number corresponding to the day of the week, assuming our week starts from Sunday. Please check out this article to learn how to combine text with a formula-driven value. Now you can go ahead and remove column A if you want to. Then right click on any of the dates and select 'Group.' A dialogue box pops up. See screenshot: Note: By using Format Cells, you have just changed the format in which the dates are displayed in the cells. Looking for ways to know how to convert date to week number of month in Excel? I found what I think is the easiest way to convert a date to its preceding Monday (first day of week). Huh? It takes a date serial number or date and lets you extract parts of the date to fit your required format. 1/5/2021 2021 1 1/4/2021 1/10/2021 Privacypolicy Cookiespolicy Cookiesettings Termsofuse Legal Contactus. 12-11-2022 14-11-2022 What I'd like to do is convert the table into weekly metrics. Canadian of Polish descent travel to Poland with Canadian passport, Adding EV Charger (100A) in secondary panel (100A) fed off main (200A). Rather is "WC 12 Oct'20" in this format, Hi can someone help with to set week commencing in below format, like if the week is start 12 Oct 2020 then I would like to see it as WC 12 Oct 2020. Is it possible to achieve it? Returns the week number of a specific date. 1/2/2021 2021 53 1/3/2022 1/9/2022 Return_type Optional. Each video comes with its own practice worksheet. Ideal for newsletters, proposals, and greetings addressed to your personal contacts. We will convert all the dates in the above sample dataset into corresponding days of the week. Hopefully, the WEEKNUM formulas you have learned today will prove useful in your worksheets. Please keep in mind this specific formula is only for year 2016 (as per your requirements), and it won't work correctly for other years. 30-10-2022 30-10-2022 I don't see any pattern in your data. How to Convert Month Number to Month Name in Excel, How to Convert Days to Years in Excel (Simple Formulas). On the Data tab, find the Data Tools group, and click Text to Columns. ex. ISO week numbers, start on the Monday of the first week in a year with a Thursday. Starting with Excel 2010, you can generate an ISO week number using 21 as the return_type: Starting in Excel 2013, there is a new function called ISOWEEKNUM. . Following the 1st day of the month the week begins on Monday. Hence, it should not bring any date from any other month. =WEEKNUM (A1,2)-WEEKNUM (DATE (YEAR (A1),MONTH (A1),1),2)+1 The formula works as follows: Extract the 'week of year' from the date. Then right click on any of the dates and select 'Group', A dialogue box pops up. Follow the steps given below to do it on your own. Any ideas whether to use an ISOWEEKNUM function or a Format Cells option: Do not waste your time on composing repetitive emails from scratch in a tedious keystroke-by-keystroke way. I was thinking =today()-14. however this would not show the Sundays date unless it was exactly 14 days prior. Is there a generic term for these trajectories? Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, in the year 2016, the first Thursday is January 7, and that is why week 1 begins on 4-Jan-2016. By clicking Post Your Answer, you agree to our terms of service, privacy policy and cookie policy. The easiest way to understand how this works out is to write out (or put in Excel) a series of dates and then write the numbers 1 through 7 as you go down the dates. My year begins on a Sunday, December 29th (cell B1) and ends on Saturday, January 4th (cell B4) The Last Day of the Week. How to Convert Date to Day of Week in Excel? Here, we will extract the day from the given date and the first day of the month the date falls within. How do i convert weekday(Monday, Tuesday,Wednesday) to date of the year e.g lets say i have week number 2 in one cell and Year 2016 in another cell. Example: The video offers a short tutorial on how to convert date to week number of that particular year in Excel. Copy this to the rest of the cells in the column by dragging down the fill handle or double-clicking on it. See the syntax for this function in more detail and choose the system that works best for you. If you follow the first method, we have shown you will get the week number from a date according to the calendar. Lets say the Start of Week1 is 3rd February 2018. =DATE(A2,1,1) - WEEKDAY(DATE(A2,1,1),1) + (B2-1)*7 + 1, End date: Why did DOS-based Windows require HIMEM.SYS to boot? How can a project start week be determined as a week nr for excel, otherwise week 3 starting project shows "-4" for task A instead of 49. How can i write a formula to start week as Wednesday to Tuesday (The week start day will be whatever the start day) and the dates to form Week-1,Day-1, on first date preceded by Now, we will convert the days to week numbers of the month in Excel by dividing the values of days by 7. So if I look at 7/7/2016 I want the formula to produce the date of Monday in the same week which would be 4/7/2016. To get the week number from a date, you can use the WEEKNUM function. So if the week starts with 1 on Sunday for example, its just that day in week 1? The vlookup function will search your database for the values (average weekly value) corresponding to you chosen list of dates. Example 1: Convert Date to Abbreviated Day of Week. Press Enter to apply the formula. The last week would be Dec 31 to Jan 6, 2021. It has two arguments, the 1st is required and the 2nd is optional: Here is a complete list of the return_type values supported in WEEKNUM formulas. We provide tips, how to guide, provide online training, and also provide Excel solutions to your business problems. Hello! I want to know the week number considering the switch of years. The top 'auto' section shows the first and last dates in your piot table; this should be fine to leave as-is (unless you want to change those, as well). Therefore, it cannot be used within SUMPRODUCT or any other array formula like the MONTH function in a similar scenario. Hello! And, visit ExcelDemy for many more articles like this. 09-11-2022 14-11-2022 I was seeking a formulas that would tell me for today's date (or any date) what is the first day of the week, base on a Sunday - Saturday week. But, what if you actually want the day of the week in plain English? Hi, my financial week starts from 1st Sunday of February month each year. Let's say your days are in Column B and data starts in row 2 (since row1 is headers). Then, we will subtract the values and add 1. 07-11-2022 14-11-2022 Thanks Svetlana. A positive value for days yields a future date; a negative value yields a past date. In this case, I use MOD(). In this short tutorial, we will briefly talk about the syntax and arguments of Excel WEEKNUM, and then discuss a few formula examples demonstrating how you can use the WEEKNUM function to calculate week numbers in your Excel worksheets. The first week of 2020 - from 30.12.2019 to 4.01.2020, Hi, =MAX(DATE(A2,1,1), DATE(A2,1,1) - WEEKDAY(DATE(A2,1,1),2) + (B2-1)*7 + 1), End date: The farther you go into a week, the bigger the WEEKDAY() value is. Explore subscription benefits, browse training courses, learn how to secure your device, and more. The WEEKNUM function takes a date and returns a number between 1 and 54 that corresponds to the week of the year. The best answers are voted up and rise to the top, Not the answer you're looking for? Input = Year+Month+Week(1 to 6) I sincerely appreciate any help that can be offered to help me solve this problem: I'm dealing with reports that provide only week numbers and years - I believe the week numbering system is the same as Excel's WEEKNUM type 21 (ISO, Mon-Sun). If you need to, you can adjust the column widths to see all the data. Expecting output for Jan 3 - 9 as week #1, Jan 10 to 16 = week #2 and so on. As you see, our formula consists of 2 parts: In the ISO week numbering system, week 1 is the week containing the first Thursday of the year. Example : 8/11/2016 to CW45'16. Parabolic, suborbital and ballistic trajectories all follow elliptic paths. Here, we will extract the day from the given date and the first day of the month the date falls within. Why are Dates Shown as Hashtags in Excel? (in format 1-52). converting a week number to a date? Find all links in your document, get them verified, correct invalid ones and remove unnecessary entries with a click to keep your document neat and up to date. This function will round up the decimal value into a value of 0 decimals. 11-11-2022 14-11-2022 Once again I'm the ONLY human on the ENTIRE PLANET who wants to have a cell with a Mon-Sun in date format in ONE CELL!!!!!! Download 5 Useful Excel Templates for Free! I had to change all the , commas to ; semicolon for it work in Excel 2010, Is there a way to use =WEEKNUM(TODAY()), but have it display in the cell with additional text before and after, i want the text to stay the same, but i want the Week Number to auto update each time someone opens the spreadsheet. =DATE (B5,1,-2) - WEEKDAY (DATE (B5,1,3)) Now, you can easily find the last date of the week by using the following formula: =E5+6 Similarly, you could use the previous formula and add six with that to get the end of the week. Please try the following formula: Here the month number is calculated from the last day of the week. =DATE(A7,1,1) - WEEKDAY(DATE(A7,1,1),1) + (B7)*7 + 1. 2 Answers Sorted by: 5 The Thursday of week 1 will always be the first Thursday of the year, then you obviously add 7 for each week after that, so you can get the Thursday date with this formula =DATE (A1,1,1+A2*7)-WEEKDAY (DATE (A1,1,3)) with Year in A1 and ISO week number in A2 Share Improve this answer Follow answered Oct 23, 2014 at 13:00 The WEEKDAY function provides the number of the corresponding days. 1/7/2021 2021 1 1/4/2021 1/10/2021 But, we need to look at the day of the month and check if it is less than 15. One of the formulas above didn't work for me on dates in 2016. Most often you would use it in combination with other functions to perform various calculations based on the week number, as demonstrated in further examples. Anyone who works with Excel is sure to find their work made easier. 11-11-2022 14-11-2022 SWITCH function takes this number and finds . "Signpost" puzzle from Tatham's collection. Browse other questions tagged, Start here for a quick overview of the site, Detailed answers to any questions you might have, Discuss the workings and policies of this site. How to convert to date ? Thanks Mike - the formula for week end date wasn't working for me either, and your formula solved it. Hi! Thats right. 03-11-2022 07-11-2022 Number of the week in the year that 3/9/2012 occurs, based on a week beginning on Monday (the second argument, 2). Note. But what if you are looking for the opposite, i.e. The serial_num argument must have a . In VBA I want to convert a date as 03/11/2017 (DD/MM/YYYY) into the week number for that date. Convert Date to Day-Number Using WEEKDAY Function 4. Hi! What differentiates living as mere roommates from living in a marriage-like relationship? Then, this is the right place for you. How can I get this done using formulas.. Pl help. Analytics Demystified are recognized across the globe for our expertise and commitment to the digital analytics community. Alternatively, you can select the dialog box launcher in the, This will display a number of formatting options on the right side, under, Type the formula: =CHOOSE(WEEKDAY(A2),Sun,Mon,Tue,Wed,Thu,Fri,Sat). Specify exactly what you want to get with your formula. I use Excel 2010 so I had to convert all the ; and also it's a Danish version So I had to change the Max to Maks, Date to Dato, and weekday to Ugedag. Founded in 2007 by Eric T. Peterson, author of "Web Analytics Demystified", the company provides strategic guidance and tactical support to many of the best known companies in the world. It will find the value of Day from the Date. Best regards, This comprehensive set of time-saving tools covers over 300 use cases to help you accomplish any task impeccably without errors or delays. Note the 0 in the else clause of the IF statement is not necessary, since the default else value is FALSE which, when converted to a number, is 0. A have a column with Year (in format 2021.01.01), a Week Nr. I have a question. All rights reserved. First, let us understand these two functions individually. July 3 = week 1 e.g [2020 July Week2] = [Sun, 05-Jul-2020] & [Sat, 11-Jul-2020], We calculate the date by the number of the month and the number of the week in this month -, =CONCATENATE("[",TEXT(DATE(A12,C12,7*(B12))-WEEKDAY(DATE(A12,C12,1),1)-5,"ddd, dd-mmm-yyyy"),"] & [",TEXT(DATE(A12,C12,7*(B12))-WEEKDAY(DATE(A12,C12,1),1)-5+6,"ddd, dd-mmm-yyyy"),"]"). Mail Merge is a time-saving approach to organizing your personal email events. Example: Here, we extracted the day from the given date as 24 and the first day of the month the date falls within as 23. By default, January 1, 1900 is serial number 1. I want to highlight dates using conditional formatting based on numbers of weeks i.e. Example:- Secondly, the SWITCH function selects the string from the string list and converts the number into text format. appreciated. 12/30/2020 2020 53 12/28/2020 1/3/2021 15-11-2022 21-11-2022 I need excel formula to get one fixed string after every 3 weeks in calendar. if you apply =TEXT(A2, dddd), then it will return Saturday. 1/10/2021 2021 1 1/4/2021 1/10/2021. You can find the detailed instructions in Changing date format in Excel. If Return_type is out of the range specified in the table above, a #NUM! By adding this column, I can create a pivot table that can easily generate weekly data for whatever metrics are in the spreadsheet. WEEKDAY(cell,3) returns number from 0-6 for days Monday-Sunday, simply subtracting that from a given date will result in the preceding Monday. We pull the year using the YEAR() function on the date were converting, the month using the MONTH() function on the date were converting, and then simply hard code the day as 1:. Your email address is private and not shared. To do this, you can use the WEEKDAY() function. Anybody who experiences it is bound to love it! e.g date of surgery: 19/8/2020. =INT (A1/100)*7+DATE (MOD ( [A1,100),1,1)-WEEKDAY (DATE (MOD (A1,100),1,1))-5 the subtraction of the weekday ensures you return a consistent start day of the week. For me to be able to help you better, please describe your task in more detail. Can you help please, hi i want formula that week start from april-16 and end to march-17 1-52 count. I need formula that can display week number with month in excel chart, please assist. Make sense (its confusinguntil its not)? Explain - "formula like this" - what formula are you talking about? My defined name plannedDate refers to a cell range containing only date formatted cells. bring the week number to a cell using formula A1=WEEKNUM(date) and concatenate that with the text "Week"(=CONCATENATE("Week",A1)). As soon as get to 1/20/2013 (Sunday), Im in a new week a week that ends on 1/26/2013. Hello! Here it converts the date to the text format M/D/YYYY, and you can change the format as you need. http://www.ozgrid.com/forum/. . Arin Islam, Thank you in advance for your help. My Excel life changed a lot for the better! Tags: Convert to Number in ExcelDATE FunctionDAY FunctionIF FunctionINT FunctionISOWEEKNUM FunctionMONTH FunctionROUND FunctionWEEKDAY FunctionWEEKNUM FunctionYEAR Function. I need a excel formula to do a weekly planning and should change the weeks automatically if it exceeds week 52 to week 1 ( if the plan ends somewhere next year) . 12/27/2020 2020 52 12/21/2020 12/27/2020 While Microsoft Excel provides an array of functions to work with weekdays, months and years, only one is available for weeks - the WEEKNUM function. Its also easily adjustable to find preceding Sunday. z o.o. Select the cells that hold each date by clicking and dragging your mouse over them. In your Excel spreadsheet, select all of the dates you want to change to text. Let us put the two functions CHOOSE and WEEKDAY together to convert date to day of the week. Please note that the formula returns the date as a serial number, and to have it displayed as a date, you need to format the cell accordingly. Here are the steps to follow: Heres what you should get as the result: To understand how this formula worked, we need to break it down: Thus we get the day corresponding to the date in A2 (in our example) to be sat. =DATE (B5, 1, -2) - WEEKDAY (DATE (B5, 1, 3)) + C5 * 7+6 1.1 Mon-Sun Week (Starts from Jan 1) So, if you subtract the WEEKDAY() value from the actual date, you will get the same value 7 days in a row, at which point the value will jump seven days. Morgan, Hello, I need a formula to sort out the weeks in sequent way .. meaning after week 1 I get week 10 and 11 etc.. how can I get week 1, then week 2 10-11-2022 14-11-2022 A date within the week. I love the program and I can't imagine using Excel without it! As an interesting aside, it would be understandable if this formula broke for the month of December. The Format Cells feature provides a great way to directly convert your dates to days of the week while replacing the existing dates. Is there a way to convert a week number (i.e 19) to a date format (1/23/2022) given current date? These formulas are similar to the above ones with the only difference that they are written for Sunday - Saturday week. Our videos are quick, clean, and to the point, so you can learn Excel in less time, and easily review key topics when needed. Go through the steps given below to do it on your own. The planning start date is 07/03/2019 later it will become to =weeknum and the plan buildup in weeks to chase / followup weekly. Im sorry but your task is not entirely clear to me. 1/4/2021 2021 1 1/4/2021 1/10/2021 Short story about swapping bodies as a job; the person who hires the main character misuses his body, Passing negative parameters to a wolframscript. It doesn't seem to have been answered yet. Use of ISOWEEKNUM Function to Convert Date to Week Number of Month, 3. I can't thank you enough sir!!! The TEXT function applies the date_format_code that you specified on the provided date and returns a text string with that format. Hello Anyway, I will do my best to provide meaningful explanation for those who are curious to get down to the bottom. =WEEKDAY ("24/5/2021") And in the selected cell, you will see the day of the week for your date as a number. For instance, a lot of companies pay their employees every two weeks. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. 1) Input = 25.3, Output = 15/06/2022 This smart package will ease many routine operations and solve complex tedious tasks in your spreadsheets. I mean if I enter date it should give me work week number. Return types 11 through 21 are supported in Excel 2010 and Excel 2013 only. How to get week number from date in Excel, How to get a week number in a month (from 1 to 6), How to highlight cells based on the week number, Excel conditional formatting based on another cell value, Excel EDATE function to add or subtract months from date, Compare 2 columns in Excel for matches and differences, CONCATENATE in Excel: combine text strings, cells and columns, Create calendar in Excel (drop-down and printable), Calculate difference between two dates in days, weeks, formula that converts week number to date, Other ways to convert week number to date in Excel, Monday (used in System 2, please see the details below. For reference, MONTH(plannedDate) returns the correct value between 1 and 12. In this case, If I select Jul-21-2020, I want to receive 25 while using WEEKNUM function. How to Get the First Day Of The Month In Excel? Group the dates by days and set them at 7 day intervals. In the final method, we will use the INT and DAY functions to convert date to week number of month in Excel. #1 - WEEKNUM Function: Calculate the Week Number of the Year We can use the WEEKNUM function to quickly determine how many weeks have elapsed from January 1st to a given date in the same year. How to Compare Two Columns in Excel (using VLOOKUP & IF), Multiple If Statements in Excel (Nested Ifs, AND/OR) with Examples, How to Center the Worksheet Horizontally on the Page in Excel, How to Merge First and Last Name in Excel, How to Remove Commas in Excel (from Numbers or Text String), Why does Excel Open on Startup (and How to Stop it), How to Unsort in Excel (Revert Back to Original Data), d one or two-digit representation of the day (eg: 3 or 30), dd two-digit representation of the day (eg: 03 or 30), ddd the day of the week in abbreviated form (eg: Sun, Mon), dddd full name of the day of the week (eg: Sunday, Monday). 26-02-2023 27-02-2023 Can anyone explains to me the logic behind the formula: DATE(A1,MONTH(B1),1) - WEEKDAY(DATE(A1,MONTH(B1),1),1)+ (C1-1)*7, Sorry ,Just small correction, My week starts on Saturday. Home How to Convert Date to Day of Week in Excel? In Microsoft Excel, you can extract a day of week from a date by using the WEEKDAY function. For a quick look at 7-day chunks, the grouping is the faster option to build. We can do it by going through some simple steps. Excel MATCH Function Not Working, How to Create a Weighted Sales Pipeline in Excel, [Fixed] Excel Failed to Launch in Safe Mode (4 Easy Solutions), SUMIFS to SUM Values in Date Range in Excel, Formula for Number of Days Between Two Dates, After that, you will get the values of the. weeknum= 2 and day is Sunday for every month. To convert a week number to a date, try this formula with WEEKDAY function: =MAX(DATE(YEAR(TODAY()),1,1),DATE(YEAR(TODAY()),1,1) - WEEKDAY(DATE(YEAR(TODAY()),1,1),2) + (A2-1)*7+1). For the fully written out weekday ($A3 is the cell with 1/15/2013 in it you would just drag this formula down, or, if youre using an Excel Table, it would autofill): Note: If you simply want the date to bedisplayed as the weekday, you dont need a formula at all you can simply change the cell formatting to a custom format of dddd (for the full weekday) or ddd (for the 3-letter weekday). Now, follow the steps given below to do it on your own dataset. To subscribe to this RSS feed, copy and paste this URL into your RSS reader. Why refined oil is cheaper than cold press oil? - James McLeod. The WEEKNUM function accepts two arguments , serial_num and return_type. =IF(WEEKNUM(D1,1)>5,WEEKNUM(D1,1)-5,WEEKNUM(D1,1)+48).
