Add only working days in grid style using Microsoft Excel

Super User Asked by user3843150 on January 1, 2022

I like to have a calendar date view of the five working days in Excel.

I manually built a first row with dates from 30th July 2018 to 3rd Aug 2018 (Mon to Fri, which are working days) and a second row with dates from 6th Aug 2018 to 10th Aug 2018.

I want to do the same using some inbuilt functions or through automation.

Unfortunately, I’m unable to add images as I am getting a warning saying I need 10 reputations to add an image. Therefore, I have tried to depict the same below as if it were in Excel:

A        B        C        D        E 

1/10/18  2/10/18  3/10/18  4/10/18  5/10/18

8/10/18  9/10/18  10/10/18 11/10/18 12/10/18

4 Answers

  1. Add a date in the first cell.

  2. Click and drag the cell (horizontally in your case) from the bottom right corner to AutoFill it, for several cells.

  3. When you release the mouse button, Excel fills the cells with calendar days but also displays an Auto Fill Options box.

  4. Click the box and then select Fill Weekdays.

  5. Excel removes any dates that fall in the weekend and only dates for weekdays will be displayed.

Answered by CrazyEyesDave on January 1, 2022

It can be done like this. Formulas in first row with data will be deferent than all the rest. Effect can be achieved by coping/pasting the second row. Rows can be skipped, but you need to be consistent with the number of rows skipped in one copping( gap between the rows needs to be fixed number).

enter image description here

Answered by IGRACH on January 1, 2022


1. Use the first row to add labels of day (M, T, W, TH, F).
2. In cell A2 write the starting date (01/10/2018).
3. In cell B2 write the formula [@M]+1.
4. Drag the formula from cell B2 to cell E2.
   Formulas: B2=>[@M]+1, C2=>[@T]+1, D2=>[@W]+1, E2=>[@TH]+1.
5. In cell A3 write the formula A2+7.
6. From that point use block repeating from right to down until required.

The image depicts the procedure

enter image description here

Answered by ArBR on January 1, 2022

One possible method using very simple formulas:

  • Enter your starting date in the top-left cell (A1): 1/10/18.
  • Enter the following formula in the cell next to it (B1): =A1+1.
  • Copy B1 to C1, D1 and E1.
  • Enter the following formula in the first cell on the next row (A2): =E1+3.
  • Copy B1, C1, D1 and E1 to B2, C2, D2 and E2.
  • Duplicate the newly completed row (2) as many times as desired.

Sample result:

enter image description here

Answered by simlev on January 1, 2022

Add your own answers!

Related Questions

miniDLNA (ReadyMedia) disconnects after few minutes

1  Asked on December 13, 2021 by sebastian-schlicht


Open URL with Windows Explorer

4  Asked on December 13, 2021 by user1001972


Excel PivotTable Date Grouping in 24-hr Center

1  Asked on December 13, 2021 by brian-silva


Unknown IP address trying to log on to my web server

1  Asked on December 13, 2021 by andrew-morton


Why is reaver not guessing the correct PIN?

2  Asked on December 13, 2021 by creative-magic


Windows 10 only boots after running chkdsk /f

0  Asked on December 13, 2021 by timvk


mv: rename X to Y: No such file or directory

1  Asked on December 11, 2021 by roccobarbi


Ask a Question

Get help from others!

© 2023 All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP