How to make perpetual calendar with Excel

1

First, create a new Excel file.

2

Enter the corresponding formula in the cell area at the red box position in the chart.

Among them,

B 1

= Today ()

F 1

= IF（WEEKDAY（b 1，2）= 7，“DAY”，WEEKDAY（b 1，2））

H 1

= Now ()

In the second row, cells A2 to H2

Enter the formula separately.

Aortic second sound

= IF（f 13 = 2，IF（OR（d 13/400 = INT（d 13/400），AND（d 13/4 = INT（d 13/4），d 13/ 100 & lt； & gtINT（d 13/ 100）），29，28），IF（OR（f 13 = 4，F 13=6，F 13=9，F 13= 1 1），30，3 1）

B2

=IF (working day (date ($D$ 13, $F$ 13, 1), 2)= B3, 1, 0).

C2

=IF (working day (date ($D$ 13, $F$ 13, 1), 2)= C3, 1, 0)

D2

=IF (working day (date $ 13, $ 13, 1 2)= D3, 1 0)

E2

=IF (working day (date ($D$ 13, $F$ 13, 1), 2)= E3, 1, 0)

Second generation

=IF (working day (date ($D$ 13, $F$ 13, 1), 2)= F3, 1, 0)

G2 Esports e-sports club

=IF (working day (date ($D$ 13, $F$ 13, 1), 2)= G3, 1, 0)

deuterium

=IF (working day (date ($D$ 13, $F$ 13, 1), 2)= H3, 1, 0)

Enter formulas in cells B6 to H6 respectively.

B6

= IF（B2 = 1， 1，0）

Hexane quaternary amine bromide

= IF（B6 & gt； 0，B6+ 1，IF（C2 = 1， 1，0））

D6

= IF（C6 & gt； 0，C6+ 1，IF（D2 = 1， 1，0））

E6

= IF（D6 & gt； 0，D6+ 1，IF（E2 = 1， 1，0））

F6

= IF（E6 & gt； 0，E6+ 1，IF（F2 = 1， 1，0）

G6

= IF（F6 & gt； 0，F6+ 1，IF（G2 = 1， 1，0））

H6

= IF（G6 & gt； 0，G6+ 1，IF（H2 = 1， 1，0））

Enter formulas in cells B 10 to H 10 respectively.

B 10

= IF（H9 & gt； =A2，0，H9+ 1）

C 10

= IF（b 10 & gt； =$A$2，0，IF（b 10 & gt； 0，B 10+ 1，IF（C6 = 1， 1，0））

D 10

= IF（c 10 & gt； =$A$2，0，IF（c 10 & gt； 0，C 10+ 1，IF（D6 = 1， 1，0））

E 10

= IF（d 10 & gt； =$A$2，0，IF（d 10 & gt； 0，D 10+ 1，IF（E6 = 1， 1，0））

F 10

= IF（e 10 & gt； = $ A $ 2,0，IF（e 10 & gt； 0，E 10+ 1，IF（F6 = 1， 1，0））

G 10

= IF（f 10 & gt； =$A$2，0，IF（f 10 & gt； 0，F 10+ 1，IF（G6 = 1， 1，0））

H 10

= IF（g 10 & gt； =$A$2，0，IF（g 10 & gt； 0，G 10+ 1，IF（H6 = 1， 1，0））

Finally, enter the graphic formula in cells B7 and H9.

Enter words and numbers in the cell area at the position of the green box in the chart.

In the first column, enter 1900 to 2050 in turn.

In column J, enter the month number in turn, from 1 to 12.

This is the effect after all formulas and words are input.

Next, we need to beautify.

First, open the view options and uncheck the grid lines.

Hide grid lines.

Set the calendar border and center font.

Show the title font more prominently.

Set the same font color and background color.

Hide what you don't need to show.

Finally, in order to realize the date query function.

You need to set data validity in cell D 13.

In cell F65438+March, data validity is also set.

Finally, there is one more question. The time of unit H 1 cannot be updated in real time.

We can update it at any time by pressing F9.

Of course, you can also use VBA to update in real time.