Add only working days in grid style using Microsoft Excel

Question

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

score 1 · Answer 1 · edited Jul 24 '20 at 16:28

Add a date in the first cell.
Click and drag the cell (horizontally in your case) from the bottom right corner to AutoFill it, for several cells.
When you release the mouse button, Excel fills the cells with calendar days but also displays an Auto Fill Options box.
Click the box and then select Fill Weekdays.
Excel removes any dates that fall in the weekend and only dates for weekdays will be displayed.

score 0 · Answer 2 · answered Sep 14 '18 at 13:18

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:

score 0 · Answer 3 · answered Sep 14 '18 at 14:16

B)

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

score 0 · Answer 4 · answered Sep 14 '18 at 14:44

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).

Add only working days in grid style using Microsoft Excel

4 Answers4