"+1" last denotes the number of days after the working date. How to count the number of weekends/weekdays between two Kutools for Excel - Includes more than 300 handy tools for Excel. We can use Conditional Formatting to analyze the date in a range of cells, and if the date lies on a weekend, highlight it. Use the below formula in Conditional Formatting to only highlight dates that occur on Sunday: In case you want to highlight specific days of the week, you can do that using a simple OR formula with the WEEKDAY formula in conditional formatting. Table showing Weekdays and Alternating Saturdays with dates beginning 4/11/2022 I would like to create a table in Excel Windows 10 that using a formula to Here I am following the steps that Ive used earlier to populate all the dates between a start and end date. You must log in or register to reply here. So, only those dates that occur on weekends (Sat or Sun) are highlighted. How to Auto Populate Dates Between Two Given Dates in Google Sheets, Increment Months Between Two Given Dates in Google Sheets, Array Formula to Lookup Date Between Two Dates in Google Sheets, How to Format Date, Time, and Number in Google Sheets Query, How to Use IF Function in Google Sheets Query Formula, Find and Lookup Target Sum Reached Row in a Column in Google Sheets, Multiple CONTAINS in WHERE Clause in Google Sheets Query. All shopping are easy to customize and impression. Please let me know, it will be very helpful. Back to, Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%, Convert Between Cells Content and Comments, Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier, This comment was minimized by the moderator on the site, Count the number of specific weekdays/weekends between two dates, Use formula to count the number of specific weekday in month, Use VAB to count the number of specific weekday in a month, Count numbers of all weekends/weekdays/specific day of week between two dates, Count the number of days except Sunday/weekends in Excel, Calculate net work hours between two dates excluding weekends or holidays in Excel, Add days to date including or excluding weekends and holidays in Excel. Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. NETWORKDAYS The same steps (covered above) with a slight change in formula can also be used to highlight only Sunday dates or any specific day of the week. 2. Start with a spreadsheet that contains the last date of the previous year and a list of your organizations holidays. WebThis article describes the formula syntax and usage of the WEEKDAY function in Microsoft Excel. Excel Click the Format button. See the output. Now you have learned, in Google Sheets how to list all Sundays from a start and end date. DaysRequired. The winning numbers for Saturday night's drawing are 16, 53, 54, 57, 65, and the Powerball is 8. All shopping are easy to customize and impression. (2) In above formula, 1 stands for Sunday. WebTo generate a dynamic series of dates that are weekends only (Saturday and Sunday), you can use the WORKDAY.INTL function. Working days exclude weekends and any dates identified as holidays. Note: The other languages of the website are Google-translated. Thank you for the formula. Tip:To calculate the serial number of the date before or after a specified number of workdays by using parameters to indicate which and how many days are weekend days, use the WORKDAY.INTL function. First, see the formula. A5 would be Sun-Jan-15-2017. But with WORKDAY.INTL, you can do the same thing with a WAY simpler formula. How could I modify this to have two dates populate in the same cell, so a week (Sunday-Saturday) is listed? In the Formulas Helper dialog, please do as follows: (1) Select Statistical from the Formula Type drop-down list; (2) Click to select Number of working days between two dates in the Choose a formula list box; (3) Enter the start date in the Start Date box (you can also refer a date cell); (4) Enter the end date in the End Date box (you can also refer a date cell); (5) Click the OK button. As a smart reader pointed out recently, you can do the same thing with the WORKDAY.INTL function and a much simpler formula: This takes advantage of what I call the "mask" feature of WORKDAY.INTL, which allows you to designate *any* day of the week as a weekend. Figure E Highlight weekends and holidays. I want to be able to put in my own start and end dates and then get the actual DATES (not the count) returned to me. Our videos are quick, clean, and to the point, so you can learn Excel in less time, and easily review key topics when needed. The trick is of course is finding them :). How do I write this formula? Note: "=5" denotes the number of working days in the week. TrumpExcel.com Free Online Excel Training, FREE EXCEL TIPS EBOOK - Click here to get your copy, Highlight Weekend Dates Using Conditional Formatting, How to Add or Subtract Days to a Date in Excel (Shortcut + Formula), Highlight Rows Based on a Cell Value in Excel (Conditional Formatting), Highlight the Active Row and Column in a Data Range in Excel, How to Calculate the Number of Days Between Two Dates in Excel, How to Autofill Only Weekday Dates in Excel (Formula), In the Styles group, click on Conditional Formatting icon, In the options that appear, click on New Rule option, In the New Formatting Rule dialog box, click on Use a formula to determine which cells to format. You are using an out of date browser. Hi Prashanth, I have an issue with an IFS function working in cell C4 of the undermentioned sheet. WebMonth 2022 Calendar (PDF Word Excel) This February 2022 per calendars are generic, blank, press their week beginnt on Sunday. Saturdays and Sundays (Shift 4 is the $ sign). (3) To calculate the total number of days worked enter the following, I don't know what happened to my formula and the rest of my message above. WORKDAY function - Microsoft Support =query(ArrayFormula(TO_DATE(row(indirect("E"&A1):indirect("E"&A2)))),"Select Col1 where dayOfWeek(Col1)=1"), Functions Used: Query | ArrayFormula | To_Date | Row | Indirect. It does work for 2016, but when entering 1/12017, it identifies the first Monday of the month to be 1/9/17, when it's actually 1/2/17. Select range and press Ctrl+Shift+4 to format cells as currency. Can someone please assist me? Save this code and return to the worksheet, then in a blank cell, enter this formula = TotalDays(year, month, 1) . It all lies in the Query use. Do you know how to list all Sundays from a start and end date in Google Sheets? The Power Play was 2X. THANK YOU VERY MUCH!!! As the date returns to the 23rd, refresh the formula to the following 22nd? Save my name, email, and website in this browser for the next time I comment. How to Generate a List of Business Days in Excel - dummies Finding List of Dates that occur on Saturdays & Sundays? The function, Hi, Nihal, I was hoping you could show me your expected result based on sample data in a Sheet. Follow these easy steps to disable AdBlock, Follow these easy steps to disable AdBlock Plus, Follow these easy steps to disable uBlock Origin, Follow these easy steps to disable uBlock. With the Thursday date in B2, the formula returns FALSE it is not Thanks for the follow-up. The WORKDAY function syntax has the following arguments: Start_dateRequired. Why are my comments not published completely???? Hi Prashant How can we add multiple Arrayformula in the same query? Saturday, January 1st, 2022 (1/1/22) 2. I have two workers at work who works different days. Back to, Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%, Convert Between Cells Content and Comments, Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier, This comment was minimized by the moderator on the site. And then go ahead based on your counting types. to highlight weekends and holidays in Excel You must have JavaScript enabled to use this form. I had a question about it. 1. Hi OssieMac, I probably use images in these forums most than most and should write up a whitepaper on the procedure, but here is the CliffsNotes version. If the result of that formula is TRUE, then the cell is highlighted in the specified color (yellow in this example), and if the result of the formula is FALSE, then nothing happens. List all Mondays / Fridays in a month or year with formulas, List all Mondays / Fridays in a month or year with Kutools for Excel. The formula will return the dates 4-Sep-2022, 4-Sep-2022, 11-Sep-2022, 11-Sep-2022, 18-Sep-2022, 18-Sep-2022, and so on. Table showing Weekdays and Alternating Saturdays with dates WebSummary. VBA to automatically populate rolling rota - Help?! I am just modifying the above formula little bit to accommodate Saturdays. Use WORKDAY to exclude weekends or holidays when you calculate invoice due dates, expected delivery times, or the number of days of work performed. Ex: typed 2017 on A1. Description Returns the day of the week corresponding to a date. There needs to be a second IF function:=IF(WEEKDAY(EOMONTH(A2,-1)+1)=1,EOMONTH(A2,-1)+2,IF(WEEKDAY(EOMONTH(A2,-1)+1)=2,EOMONTH(A2,-1)+1,EOMONTH(A2,-1)+(8+MOD(2,7))-WEEKDAY(EOMONTH(A2,-1)+1))). The formula should be: You need to consider what day you're starting from and ending with! However, the formula adds 10-1=9 days and gives you the 9th January as the first Monday. Most of the time, it will equal the number of weeks in a year, but that's only true for some of the days of the week. Full feature free trial 30-day, no credit card required! We have a great community of people providing Excel help here, but the hosting costs are enormous. The answer to this question is not always simple. (0 members and 1 guests), By WDN in forum Access Tables & Databases, By cdixon01 in forum Excel Formulas & Functions, By Jrub in forum Excel Programming / VBA / Macros, Search Engine Friendly URLs by vBSEO 3.6.0 RC 1, [SOLVED] generate all sundays for the year, How To Learn Excel Effectively by Helping People, http://www.excelforum.com/showthread.php?t=1172898, generate missing years of year range adjacent cell. If I wanted to create two lists by formula (i.e., without using auto-fill) that would return all the weekdays (list 1) and all the weekend days (list 2), how might I do that for 2022? i want to calculate no. The dates for which the difference is to be computed. L mt thng hiu phn phi tr Anh Quc ti Vit Nam, la Supposing, I have the following data range, and now I want to get the average orders of all Mondays, workdays or weekends. hiu, v This was a very helpful tutorial. How many Saturdays in 2022 - Convert Units gii, ti s dng tn min .vn nh mt li khng nh v xut x thng And then it returns the total number of weekdays (excluding Saturdays and Sundays) in the selected cell. We need to first work out the date of the Thursday in the week of A1. Conditional Formatting in Excel allows you to assess the value in a cell and then apply it to format if the specified condition is met. This can easily be done using Conditional Formatting with a little bit of formula know-how. =query(ArrayFormula(TO_DATE(row(indirect("E"&A1):indirect("E"&A2)))),"Select Col1 where dayOfWeek(Col1)=1 or dayOfWeek(Col1)=7"). All Saturdays, Sundays, and holidays (as defined in column F) are highlighted in red (or the color you chose in step 5). Purpose Get the number of working days between two dates Return value A number representing days. In the example formula 1, the dayOfWeek number 1 indicates Sunday. This should work: The formula will skip Saturday & Sunday in the dates. with a date format of ddd-mmm-dd-yyyy. For that please follow the below tutorial. b see screenshots: Notes: This formulas use integers to represent day of weeks: 1 is Sunday, 2 is Monday, 3 is Tuesday, 4 is Wednesday, 5 is Thursday, 6 is Friday and 7 is Saturday. How to list all Mondays / Fridays in a month in Excel? - ExtendOffice A positive value for days yields a future date; a negative value yields a past date. th We have to keep in mind that Excel automatically assumes Saturday & Sunday as weekends. Any idea how I can populate two cells with the same date while carrying on the sequence? The above steps would highlight those dates that are either a Saturday or a Sunday. 2022 That lets us create a simpler formula we can check for numbers greater than 5 (Friday). The following formulas can help you to list all Mondays or Fridays in a month or year from a given date of the month, please do as follows: 1. Let's say you want to calculate the 3rd Thursday of the month and assume the date of the first of the month is in cell A1. Web2022 Excel Calendar With Festive and National Holidays - India 2022 Calendar with India National Holidays in Excel format 2022 Excel Calendar Templates with popular and India National Holidays. Please enter the date that you want to list every Monday or Friday of the month in a cell, for example, I will enter 1/1/2016 in cell A1. Add days to date including or excluding weekends and holidays in Excel In this article, I am talking about adding days to a given date excluding weekends and holidays which means to add business days (from Monday to Friday) only in Excel. What about a formula that returns the actual date of the first Wed of each month, Or the 2nd and 3rd tuesday of each month? thng Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window. Increment Months Between Two Given Dates in Google Sheets, 2. weekday(date(2022,9,4)+int(sequence(100*2,1,0)/2))=1). JavaScript is disabled. Register To Reply. 2019 too :( first wednesday is not 09 Jan? STEPS: In the beginning, select cell C5. and so on. hi sir, For that, this article, introduces the formulas on calculating the net work hours between two dates excluding weekends and holidays in Excel. Note: The other languages of the website are Google-translated. Select a blank cell, type below formula, and press the Enter key to get the counting result. It worked for me.. fo rFridays Saturdays and Sundays List 2022 | Saturdays and Sundays List 2023 Updated December 4, 2022. How to Generate List of WeekEnd Only Dates in Excel - Office 365 In this example, I will count how many Sundays there are in June 2020, so I can apply this formula as one of below formulas, then press Enter key, and you will get how many Sundays at once. E.g. There are 53 Saturdays in the year 2022. How many Sundays in 2022? There are 52 Sundays in the year 2022. How many Saturdays in 2023? Follow these easy steps to disable AdBlock, Follow these easy steps to disable AdBlock Plus, Follow these easy steps to disable uBlock Origin, Follow these easy steps to disable uBlock. ng k tn min s gip doanh nghip, t chc hay c nhn Calendar files are in xlsx (macro-free) format and compatible with Google Docs and Open Office Calc. 2022 Excel Calendar - CalendarLabs.com Re: Formula for Wednesdays & Sundays in 2021. The employee will start a part time job only on weekends (Saturday and Sunday) for 16 days. This is perfect! [quote]Why are my comments not published completely???? Do , chng What about weekends? Is it possible? doanh nghip nhp khu v phn phi hng quc t, vic ng k tn min cng gip Then filter out the dates other than the Sundays. You must log in or register to reply here. Sometimes, we need to know how many specific weekdays between two dates. I had used the WEEKDAY formula that takes the date as the input and returns a value that tells me what day of the week that date represents. WEEKDAY WebAn optional list of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays. This article, I will talk about how to list all Mondays or Fridays in a month in Excel. As the formula is copied down, the WEEKDAY.INTL function returns the next date that is either Saturday or Sunday. If you have an older version of Excel without the WEEKDAY.INTL function, you can an alternative formula based on the WEEKDAY function: By default, WEEKDAY will return 1 for Sunday and 7 for Saturday. There needs to be a second IF function: Did not work for me on every Monday that was in a month with the first day being a Sunday. Saturday chn lm trang bn hng online vi tn min ".vn" m khng phi l cc ui This article describes the formula syntax and usage of the WORKDAYfunction in Microsoft Excel. Most years have 365 days, but a leap year has 366 days. trn Internet. Formula to find the dates only for Saturdays and Sundays | MrExcel Message Board. You can help keep this site running by allowing ads on MrExcel.com. hng ti cc trang mng x hi hay gian hng trc tuyn trn cc nn tng bn hng =INT((WEEKDAY(DATE(G2,G3,1)- G4)-DATE(G2,G3,1)+EOMONTH(DATE(G2,G3,1),0))/7). 2022-01-02 (January 2,2022) 2022-01-09 (January 9,2022) 2022-01-16 (January 16,2022) 2022-01-23 T. Solved; Copy data from workbook where filename and tab name match. Hope you have already followed my above link to understand how to list all the dates for a start and end date. Below is the formula that will highlight only those dates that occur on a Tuesday or a Thursday: The above OR formula checks whether the WEEKDAY formula for the date returns 2 or 4 (where 2 is for Tuesday and 4 is for Thursday). WebTo get the first Saturday of the month: =IF (WEEKDAY (EOMONTH (A1,-1)+1)=7,EOMONTH (A1,-1)+1,EOMONTH (A1,-1)+ (8 + MOD (7,7))-WEEKDAY Use TRANSPOSE to change the orientation. Count the number of weekends (Saturday and Sunday) between two dates in Excel In the Formulas Helper dialog, please do as follows: (1) Select Statistical from the Formula I select year 2009, and if i select from the dropdown months like January or any month, it should be displaying the list of Saturdays & Sundays respectively and Average based on day of week in Excel In Excel, have you ever tried to calculate the average of a list number depending on which day of the week? Select a blank cell you will place the counting result, and click Kutools > Formula Helper > Formula Helper to enable this feature. for example: I enterd the date 01/01/2016 ( Friday) and second date 14/01/2016( thursday) no of weeks showing = 2 weeks. S hu mt tn min gn vi thng hiu l iu khng th thiu It seems you want to list all Sundays twice in a sequence. Or every 3rd tuesday? Skip weekends while autofilling dates in excel The second part, thats the filtering part, Ill explain to you after that. Note: In this tutorial, I am not going to explain you the first part of the logic. The trick is to use a special 7-digit "mask" to filter out all dates except weekends. If the start date is 01/01/2018 and end date is 31/12/2018, the formula would return all the Sundays during the year 2018. Assuming cycle refers to month, this should work for the end date: 3.) Here you go! In this tutorial, I will show you how to highlight the weekend dates in a date data set. Important:Dates should be entered by using the DATE function, or as results of other formulas or functions. After installing Kutools for Excel, please do as follows:( Go to Download Kutools for Excel Now! The above steps would highlight those dates that are either a Saturday or a Sunday. Ive wrapped this formula with a Query formula to return only the Sundays. Here is that awesome trick. Finally, select cells A4:A8 and then drag drown for furthur dates. Sunday tr thnh mt cng c quan trng trong vic to dng s hin din v hot ng qung TaskMaster; Oct 21, 2022; Excel ti, I hope that helps. =INT((WEEKDAY($B$1-2)-$B$1+$B2)/7)+INT((WEEKDAY($B$1-4)-$B$1+$B2)/7), To help future seekers. Here's a little puzzle for youhow can you use Excel to generate a list of dates that are weekends only? lun t ho l mt thng hiu Vit Nam, Chng ti tin tng la chn tn min ".vn" bi ".vn" l For example, use DATE(2008,5,23) for the 23rd day of May, 2008. of weeks in excel between two dates, but Dose not see right weeks as per date For a better experience, please enable JavaScript in your browser before proceeding. error value. "+3" denotes the number of days on weekends. Keep up the great work! You can even populate all the weekends between the provided two dates in Google Sheets. after pressing enter. You can help keep this site running by allowing ads on MrExcel.com. WebSyntax NETWORKDAYS.INTL (start_date, end_date, [weekend], [holidays]) The NETWORKDAYS.INTL function syntax has the following arguments: Start_date and end_date Required. Excel 3. See screenshot: Note: To get the first Tuesday of the month: =IF(WEEKDAY(EOMONTH(A1,-1)+1)=3,EOMONTH(A1,-1)+1,EOMONTH(A1,-1)+(8 + MOD(3,7))-WEEKDAY(EOMONTH(A1,-1)+1)), =IF(WEEKDAY(EOMONTH(A1,-1)+1)=4,EOMONTH(A1,-1)+1,EOMONTH(A1,-1)+(8 + MOD(4,7))-WEEKDAY(EOMONTH(A1,-1)+1)), =IF(WEEKDAY(EOMONTH(A1,-1)+1)=5,EOMONTH(A1,-1)+1,EOMONTH(A1,-1)+(8 + MOD(5,7))-WEEKDAY(EOMONTH(A1,-1)+1)), =IF(WEEKDAY(EOMONTH(A1,-1)+1)=6,EOMONTH(A1,-1)+1,EOMONTH(A1,-1)+(8 + MOD(6,7))-WEEKDAY(EOMONTH(A1,-1)+1)), =IF(WEEKDAY(EOMONTH(A1,-1)+1)=7,EOMONTH(A1,-1)+1,EOMONTH(A1,-1)+(8 + MOD(7,7))-WEEKDAY(EOMONTH(A1,-1)+1)), =IF(WEEKDAY(EOMONTH(A1,-1)+1)=1,EOMONTH(A1,-1)+1,EOMONTH(A1,-1)+(8 + MOD(1,7))-WEEKDAY(EOMONTH(A1,-1)+1)). then use formula =int (b2+3) Suppose your dates from "B2". Sundays dates in 2022. The calendars are ready in multiple styles. If days is not an integer, it is truncated. And now you will get the number of Sundays between the two dates. 1. There are currently 1 users browsing this thread. That doesn't help Am I misunderstanding the formula's intent? Formula for Wednesdays & Sundays in 2021 It may not display this or other websites correctly. Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day. If you need to, you can adjust the column widths to see all the data. Hi Jaco, You. Kutools for Excel - Includes more than 300 handy tools for Excel. How to generate a report of all the days I have my child for visitation in a given year? And since I have the formula, =WEEKDAY(B2,2)>5, it checks whether the weekday value for a date is more than 5 or not. Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day. Apr 19, 2022. Array Formula to Lookup Date Between Two Dates in Google Sheets, 3. Your examples are so well presented - the right balance of concise visual aid and text explanation, which allows me to quickly understand if a function suits my needs and then implement it easily. tn min ny gip ti hng n th trng trong nc hiu qu hn!, Thng hiu thi trang ca ti c bit n nhiu nc trn And then it returns the total number of the specified weekday in the given date range. List of Saturdays and Sundays in 2022 Excel - toppetfacts.com You can also create a User Defined Function to calculate how many specific weekdays in a given year and month besides the above formula. The calendars are ready in multiple styles. Meaning, should I enter a different date to pull the 1/2/17 date? For a better experience, please enable JavaScript in your browser before proceeding. =WEEKDAY(B2,2)>5. A date that represents the start date. Normally, you can list a series of date with Fill Handle, but it cant help you to fill every Monday or Friday in a month or a year. Calendar Week Formula in Google Sheets to Combine Week Start and End Dates. Count the number of days except Sunday/weekends in Excel For example you need to count the total number of days except weekend between 2015/1/1 and 2015/10/31 for working schedule, how will you figure it out in Excel? In this formula, the dayOfWeek 1 is Sunday anddayOfWeek 7 is Saturdays. Specify the formatting (I will go with the Yellow fill color) Click OK. Click OK. how many Sundays in 2022? Sundays dates in 2022 Hi there heres a solution that doesnt need =query() to generate a list of weekdays (in this case Mondays) between your start and end dates: It seems you require to change A1 in the last part of the formula with (A1-weekday(A1))+9 to return Monday irrespective of the weekday in A1. That means she works 156 days per year, but I want excel to deduct the holidays if it is on one of her working days. ). Date 151 workdays from the start date (4/30/2009), Date 151 workdays from the start date, excluding holidays (5/5/2009). So this is how you can use a simple formula in Conditional Formatting to highlight weekend dates (or specific weekdays) in Excel. Now it returns the total number of all Saturdays and Sundays in the selected cell. This function Select a cell to insert the date, and click Kutools > Insert > Insert Date, see screenshot: 2. creating two lists of dates for 2022 (weekends, weekdays) by formula The logic here is to first populate all the dates between two given dates. For example, you may have a list of dates where you want to highlight all the weekend dates. In the Insert Date dialog box, a month calendar is displayed, you can double click the date to insert it to the cell, you can also change the date format to your need by checking Using format option to activate the date formatting list box. 1. Gn vo ti khon mng x hi/gian hng trc tuyn. For example, a list of Saturday Sunday pairs like this: A couple years ago, I found and described a formula that will do it using the WEEKDAY function and some tricky date logic handled with IF: With a date in A1, you can enter the formula in A2 and drag down to get your list of weekend dates. 6 Answers Sorted by: 13 Try this where A1 is the starting day of the month: =SUMPRODUCT (N (TEXT (ROW (INDIRECT (A1&":"&EOMONTH (A1,0))),"ddd")="Sun")) You can also use this for any day of the week just change "Sun" and any date range by replacing A1 with start date and EOMONTH (A1,0) with end date. In the Formulas Helper dialog, please do as follows: (1) Select Statistical from the Formula Type drop-down list; (2) Click to select Number of non-working days between two dates in the Choose a formula list box; (3) Enter the start date in the Start Date box (you can also refer a date cell); (4) Enter the end date in the End Date box (you can also refer a date cell); (5) Click the OK button. Here its. The number of nonweekend and nonholiday days before or after start_date. 1. Thanks again, Try So helpful, thank you! trnh khi vic tn thng hiu ca mnh b s dng cho mc ch khc. JavaScript is disabled. I have used a formula in Conditional Formatting that checks each cell in the selected range against that formula. If that is the case, you can use this formula in A1. Maybe this is somewhat difficult for us, but here, I can talk about some effective methods for you. So, itll fill the series without those days. You have entered an incorrect email address! atul. After displaying the first Monday, please go on entering this formula: =B1+7 to get the second Monday, then drag the fill handle down to list all Mondays in a month or a year as you need, see screenshot: Except the above formulas, here, I can introduce an easy tool-Kutools for Excel, with its Insert Date feature, you can insert all Mondays or Fridays in a month as quickly as you can. Platform Windows Sep 16, 2021 #1 If I wanted to create two lists by formula (i.e., without using auto-fill) that would return all the weekdays (list 1) and all the weekend If you want to list all Mondays, then replace the number 1 with 2.