anand3dinesh

If you modify your spreadsheet as follows then there is a formula that will automate the process.

**Read and understand all the steps before doing anything**

1. Cell B2 enter the date 1/1/19

2. Cell C2 enter this formula =DATE(YEAR(B2),MONTH(B2)+1,1)

3. Copy the formula in Cell C2 across cells D2 to Q2

4. Cell J2 enter the date 1/1/20

5. Custom format cells B2 to Q2 with m. This makes only the month number appear in those cells.

6. Cell B1 enter the formula =B2

7. Custom format cell B1 with yy. This makes only the year appear in cell B1.

8. Copy Cell B1 to cell J1

9. Wherever you have yellow cells enter A, blue cells B and green cells C

10. Wherever you have hatched yellow enter AE, hatched blue BE, hatched green CE

11. Construct 6 conditional formats for the block B3 to Q9 as follows:

11.1 If cell = A then text and fill is yellow

11.2 If cell = B then text and fill is blue

11.3 If cell = C then text and fill is green

11.4 If cell = AE then text and fill is yellow with hatching

11.5 If cell = BE then text and fill is blue with hatching

11.6 If cell = CE then text and fill is green with hatching

11.7 This will make the text invisible against the colored background

11.8 Alternatively you can manually color and shade the cells and then copy them around as the data changes. Either way you must as a minimum have A, B or C in the colored unhatched cells.

12. Put this formula in cell T3

=INDEX($2:$2,1,MAX(IFERROR(LOOKUP(2,1/(B3:Q3="A"),COLUMN(B3:Q3)),0),IFERROR(LOOKUP(2,1/(B3:Q3="B"),COLUMN(B3:Q3)),0),IFERROR(LOOKUP(2,1/(B3:Q3="C"),COLUMN(B3:Q3)),0))+1)

More information the workings of the Lookup part of the formula can be found by entering *excel find last non-blank cell* into a search engine

13. Custom format cell T3 with m"_"yy

14. Copy the formula in T3 into cells T4 to T9

15. The results for T8 and T9 will be wrong unless you construct a third date block for 2021 or modify the formula so that if the result of the Max function is 17 (column Q) then the formula result is 1_21.

16. If you add a 2021 block, make sure you modify the formula to include that block