Listing Dates in Excel Excluding Weekends
KATHRYN HATASHITA-LEE
TECH
Microsoft Excel provides a Function Library to help you list workdays without guesswork, and the library includes date formulas to display a series of days in a column. The Workday function enables you to list only the Monday to Friday dates for a more streamlined look without the weekends. This formula can apply to a student schedule with weekday classes or to an employee’s five-day workweek, for example.
Type "Date" in cell A1. Enter "Weekday" in cell B1.
Type the first date in cell A2 in this format: dd/mm/yyyy. For example, to enter January 24, 2014, type "24/01/2014" (without quotation marks here and throughout) in A2. Press "Tab."
Enter this formula in cell A3 and then press "Tab" to display the next date in A3:
\=WORKDAY(A2,1,D$2:D$5)
Enter this formula in B2 and then press "Tab" to display the day of the week:
\=TEXT(A2,”ddd”)
Click inside A3 to display the bold cell border and a tiny square in the lower right corner. Point over this corner to convert the cursor to the "+" symbol. Click and drag down the column to display the dates, excluding weekends.
Click B2 to display the cell border, point over the lower right corner to convert the cursor to the "+" symbol, click the corner and then drag down the column to display the Monday to Friday entries. No Saturdays or Sundays will show on your worksheet.
Save your worksheet.
- Information in this article applies to Microsoft Excel 2013. It may vary slightly or significantly with other versions or products.
- To change the date format, right-click the column header "A" to open the list and then select "Format Cells" to open the dialog box. Click the "Number" tab and then select "Date" in the Category pane to display the Type section. Select a new format, such as "14-Mar-2012" and then click "OK" to reformat the Date entries.
- If you see "#VALUE!" in the Date column, the start date is not valid. Type another start date.
Related Articles
