An Excel formula to get the week of the month B2 * 7 - adds the number of weeks multiplied by 7 (the number of days in a week) to get the Monday (start date) of the week in question. AbleBits suite has really helped me when I was in a crunch! The formula to return the Start date of the week is as follows: =DATE(A2, 1, -2) - WEEKDAY(DATE(A2, 1, 3)) + B2 * 7. Method 1: Converting Date to Day of Week in Excel using the TEXT Function The TEXT function is a great function to convert dates to different text formats. To match our financial year that we use here in Australia. I love the program and I can't imagine using Excel without it! I want to highlight dates using conditional formatting based on numbers of weeks i.e. How do I convert a calendar week into a date in Excel? I'm trying to calculate the previous 15 weeks from a current date. Convert date to text in Excel - TEXT function and no-formula ways 07-11-2022 14-11-2022 Is it possible to use this function but have the week numbers start 1st July in the year? date - WEEKDAY(date - 2)? Explain - "formula like this" - what formula are you talking about? Example:- How to calculate week ending date of the given dates/years in Excel? Its also easily adjustable to find preceding Sunday. WEEKDAY() doesnt work for this, because it doesnt tell you which of the two weeks a day falls into. On step 1 of the wizard, select the Delimited file type and click Next. The CHOOSE function lets you specify a particular outcome depending on a value. For instance, a lot of companies pay their employees every two weeks. First, we used the WEEKDAY function to find the number corresponding to the day of the week, assuming our week starts from Sunday. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts. I can't thank you enough sir!!! 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 want a single cell to show Monday-Sunday as ##/## to ##/##. The serial_num argument must have a . But, what if we want to use thelast day of the month? Create a coloumn with dates of your choice (weekly, monthly, etc). Could you please help me out, answering through my e-mail or through this page? Why? Home How to Convert Date to Day of Week in Excel? This function is, basically, a remainder function, and its main use is to calculate the remainder when one number is divided by another (for instance, =MOD(14,4) returns 2 because, when you divide 14 by 4, you get a remainder of 2). 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. So no first Monday will be return on that month and the second Monday should return 6 Feb 2023 and fourth Monday will be 20 Feb 2023 and so on using the same algorithm for every month and every year. To get the number after the decimal point, use the WEEKDAY function. How can I convert my work week number to date or vice versa. Week-2,Day-1 as so on i have dates from 2014 to 2017. So the function: =CHOOSE(3, sun,mon,tue,wed,thu,fri, sat) will return tue, because its the third value in the list. If you face any further problems, please share your Excel file with us at [emailprotected]. Can someone please help to get the week number if the week starts on January 2, 2020? As an interesting aside, it would be understandable if this formula broke for the month of December. Thank you! Returns the week number of a specific date. Use any of these ways to accomplish the result in this regard. If I understand your task correctly, the following formula should work for you: =CONCATENATE("Week of ",TEXT(A2-WEEKDAY(A2,2)+1,"mmm")," ", DAY(A2-WEEKDAY(A2,2)+1) & LOOKUP(DAY(A2-WEEKDAY(A2,2)+1), {1,"st";2,"nd";3,"rd";4,"th";21,"st";22,"nd";23,"rd";24,"th";31,"st"})), Hello,