Excel tutorials

Dynamic Calendar in World Languages

In this tutorial I will show how we can create a dynamic annual calendar that we can customize in more than one world. When I say "world languages" then I mean the name of the day of the week and the name of the month of the year. We can also change the beginning of the day to the week. In addition to this automatic dynamic calendar, we can mark the current day (red cell) as well as eventual events such as green cells. For this purpose we will use Conditional Formatting.

In the picture below, see how the dynamic calendar in Excel looks like.


Dynamic Calendar in Excel in World Languages

Language Code in Excel

In the picture below, in the 'A' column, you can see the Language codes in Excel, which can be used to automate the display of name of the day in the week or month of the year, for the selected world language.

Language Code in Excel and Country

Choose Language Code

To begin with the picture below, see 'A1' and 'A2' cells.
Cell 'A1': This cell contains Data Validation that retrieves data from Sheet 'LanguageCode'. When selecting the Language Code in 'A1' cells, names of days in the week and names of the month of the year are automatically changed.
Cell 'A2': This cell contains Data Validation by which we can change the order of the day of the week, ie, which day will begin the week.

How to change language of day or month names

 

Used Excel formulas for creating a dynamic calendar

Formulas in certain cells are the following:

'C2' cell: This cell contains the number of years for which we want to display a dynamic calendar (After entering the year number, all other dates are changed automatically).

'C4' cell: This cell contains the formula (This formula sets the first day of the relevant month, in this case it is 'January').
=DATE($C$2;1;1)

'M4' cell: This cell contains the formula (This formula sets the first date of the relevant month, in this case it is 'February').
=DATE($C$2;2;1)

'C5' cell: This cell contains the formula (This formula returns the name of the month in the set year, in this case it is 'January 2019').
=IF(C4<>"";TEXT(C4;"[$-"&$A$1&"]"&"mmmm yyyy");"")

'M5' cell: This cell contains the formula (This formula returns the name of the month in the set year, in this case it is 'February 2019').
=IF(M4<>"";TEXT(M4;"[$-"&$A$1&"]"&"mmmm yyyy");"")

'C6' cell: This cell contains the formula (This formula returns the name of the day, based on the values for the Language Code in the 'A1' cell. After changing the value in the 'A1' cell, the name language of the day is automatically changed)
=IF(C7<>"";TEXT(C7;"[$-"&$A$1&"]"&"ddd");"")

'M6' cell: This cell contains a formula (This formula returns the name of the day, based on the values for the Language Code in 'A1' cell. After changing the value in the 'A1' cell, the name language of the day is automatically changed.
=IF(M7<>"";TEXT(M7;"[$-"&$A$1&"]"&"ddd");"")

'C7' cell: This cell contains the formula (This formula returns a date based on the first date in the relevant month and is related to the order of the day of the week that is set in the 'A2' cell. Custom format cells "d" is set up in this cell).
=C4-CHOOSE(WEEKDAY(C4);0;1;2;3;4;5;6)+$A$2

'M7' cell: This cell contains the formula (This formula returns a date based on the first date in the relevant month and is related to the order of the day of the week that is set in the 'A2' cell. Custom format cells "d" is set up in this cell).
=M4-CHOOSE(WEEKDAY(M4);0;1;2;3;4;5;6)+$A$2

'D7' cell: This cell contains the formula (This formula returns the next date after the previous one in the same order. Copy this formula to 'I7' cells).
=IF(C7<>"";C7;$I6)+1

'N7' cell: This cell contains the formula (This formula returns the next date after the previous one in the same order. Copy this formula to 'S7' cells).
=IF(M7<>"";M7;$I6)+1

'C8' cell: This cell contains the formula (This formula returns the next date after the previous one.) Copy formula to across.
=IF(B8<>"";B8;$I7)+1

'M8' cell: This cell contains the formula (This formula returns the next date after the previous one.) Copy formula to across.
=IF(L8<>"";L8;$S7)+1

Setting Conditional Formatting for Certain Events

If you want to highlight specific dates for specific events then use Conditional Formatting, like this in the image below.

How to highlight vacation dates in a dynamic calendar

 

Conditional Formatting formula for highlighting a day that does not belong to a particular month is below (gray cells)
=MONTH(C7)<>MONTH($C$4)

Conditional Formatting formula for highlighting today's day (red cell)
=C7=TODAY()

Conditional Formatting formula for highlighting a day if there are certain events in a month (green cells)
=MATCH(C7;events;0)

Dynamic Calendar – US Language Code

US Dynamic Calendar in Excel

Dynamic Calendar – Croatian Language Code

Croatian Dynamic Calendar in Excel

Dynamic Calendar – Arabic Language Code

Arabic - Dynamic Calendar in Excel

Dynamic Calendar - Canadian Language Code

Canadian - Dynamic Calendar in Excel

Dynamic Calendar - Chinese Language Code

Chinese - Dynamic Calendar in Excel

Dynamic Calendar - Czech Language Code

Czech - dynamic calendar in Excel

Dynamic Calendar - French Language Code

French - Dynamic Calendar in Excel

Dynamic Calendar - German Language Code

German - Dynamic Calendar in Excel

Dynamic Calendar - Greek Language Code

Greek - Dynamic Calendar in Excel

Dynamic Calendar - Italian Language Code

Italian - Dynamic Calendar in Excel

Dynamic Calendar - Japanese Language Code

Japanese - Dynamic Calendar in Excel

Dynamic Calendar - Russian Language Code

Russian - Dynamic Calendar in Excel

Dynamic Calendar - Spanish Language Code

Spanish - Dynamic Calendar in Excel

Please note that you must correctly set absolute and relative addresses in formulas, especially if you copy the formula. Finally, in the attachment of this tutorial, you have the Download file option on which I did this tutorial.

Pay attention, you may need as a delimiter of arguments formula use a comma (,) instead of a semicolon (;). Copy formula to Notepad and with Find/Replace change semicolon to comma.