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

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

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

enter image description here

  • Is this significantly different from the answer I posted earlier? – simlev Sep 14 at 14:48
  • Is slightly similar even though uses cell-labels for dynamic cell-formulas creation – ArBR Sep 14 at 14:57

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

  • I don't see how this is different from the answer I posted one hour ago. Please show me how this answer introduces some new, needed concepts. – simlev Sep 14 at 14:51

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.