Windows region

Sessions are conducted with windows Region settings designated as Australia. Standardization ensures consistency with date and currency specifications across all Windows platforms. The Windows settings are the foundation for these items in Excel.

Region (and Language)

To verify or change your Windows Region settings in Windows 7:

1. Click the Windows Start button
2. Then, click the Control Panel item on the Start Menu
3. From the Adjust your computer’s settings dialog box (assuming that View by is set to Small Icons), click the Region and Language item
4. This will display the dialog box shown in figure 1

To verify or change your Windows Region settings in Windows 10:

1. Click the Windows Start button
2. Click the Settings item on the Start Menu, then the Time & language item
3. On the Date & time tab, select Additional date, time and regional settings in the Related settings section at the bottom
4. Then, on the Clock, Language and Region tab, select Region > Change date, time, or number formats

4. This will display the Region dialog box similar to that displayed in figure 1, but without the Keyboard and Languages tab
5. On the Formats tab, set Format: to English (Australia)
6. Leave the other settings unchanged as the default values
7.  

8. 

5. That’s all you need to do to view or change the Region settings
6. The next section looks at customization using the Additional settings… button shown in figure 1. The features of selected items are discussed in the next section.
7. Clicking Additional settings… displays the Customize Format dialog box shown in figure 2

Customize Format

The Regional Settings and Format option applies to Windows environment, and many of these properties can be customized and different formats applied directly within the Excel application.

• The Customize Format dialog box has four tabs: Numbers, Currency, Time, and Date
• The Numbers tab appears in figure 2, and three points are worthy of noting
  1. The decimal symbol / decimal mark for Australia, shown at ① is a period or full stop. Many other countries, such as those in the Euro zone use a comma instead
  2. In Australia, the comma is used as the digit grouping symbol, see ②. Digits to the left of the decimal symbol are in groups of threes. The Euro zone uses a period or full stop as the digit grouping symbol
  3. At ③ negative numbers have a leading minus sign, and decimals without an integer component have a leading zero

• 

• Tip: you can override the windows regional settings decimal symbol (decimal separator) and digit grouping symbol (thousands separator) at the application level. To do this:

  • Activate the Excel backstage view – Excel options. Click File > Options > Advanced > Editing options
  • 
  • Untick the Use system separators item
  • Change the Decimal separator to a dot (.)
  • Change the Thousands separator to a comma (,)
  • This change has effect at the application level. It will update the active workbook and new workbooks

 

• 4. Currency is simply a formatted number, and the Australian currency format (style) for a number is illustrated in figure 3. The main choices in Excel relate to Currency format (the same as the Window’s Example in figure 3) and Accounting format (with a left justified currency symbol, and a trailing space after the number).

• 

• 5. The Windows Time format for a number, with Examples, appears in figure 4a. Hours and minutes are separated by a colon punctuation mark (:)

• 

• 6. The Excel Time format for a number appears in figure 4b. This is the Numbers tab from the Excel Format Cells dialog box. In Excel, the unit of time is 1 day, and time of day is the decimal fraction of that number. For example, 12:00 noon is derived from 0.5. One Excel Time Type, shown in the figure, is linked to the Windows Region Time format – see the yellow highlighted region in figure 4b.

• 
• 7. The Windows Date format for a number appears in figure 5a
  8. Short and Long forms of the Date are marked by ④. Note that both forms include century details with the four character (four-digit) year, yyyy
  9. Point ⑤ determines the way dates with a two-digit year are interpreted, often written as yy. The default setting means that numbers in the interval from 30 to 99 are in the series 1930 to 1999, whilst numbers from 00 to 29 are in the series 2000 to 2029. This is often described as the 2029 rule, discussed further in the last section of this module. The spinner at point ⑤ allows the end year value to be customized. As there is always 100 numbers in the series, the start date will adjust automatically. The value set however is a Windows setting and stays on the machine. It is not exported with the Excel file. You should leave this value unchanged.

• 

• 10. The Excel Date format for a number appears in figure 5b
  11. Two of the Excel Date Types are linked to the Windows Region settings. These are Type: *14/03/2012, and Type: *Wednesday, 14 March 2012 and are highlighted in the figure.
  12. Default settings for the feature can be restored by clicking the Reset button at ⑥ in figure 5a

• 

The 2029 rule

• In finance and business, the best policy is to always use a four-digit year specification. Date errors can cause major problems in any time value of money calculation.
• As shown in figure 5a, the Calendar two-digit year end point can be changed
• Figure 6 for the Excel Web App demonstrates different values for the two-digit year and changes to the 2029 environment
• The feature of figure 6 are:

  1. Dates are entered in column B
  2. Column D displays the numeric value of the date by a linked cell with number format
  3. Column F uses the GetCF custom function to display Formula, Text and Value information from column B
  4. Windows Calendar values are in column G, and these points are repeated in that column
  5. With windows calendar end set as 2029, the first 4 dates 30/12/29 to 2/1/30 are entered in rows 5 to 8. The serial date numbers indicate that 31/12/29 is one century AFTER 1/1/30. In other words 2/1/30 is interpreted as 2/1/1930
  6. The Excel file is then saved and closed
  7. Windows calendar end is set to 2030, and the file opened
  8. The date 3/1/30 is entered at row 9, and is interpreted as 3/1/2030. Note that 1/1/30 and 2/1/30 do not update and remain at 1930. Again the file is saved, this time to Microsoft OneDrive, and Excel is closed.
  9. With windows calendar end remaining at 2030, the file is opened from OneDrive in Excel Online
  10. The date 4/1/30 is entered at row 10, but is interpreted as 1930

Fig 6: Excel Web App #1: an experiment with two-digit year values and different settings for the 2029 value

---

• This example was developed in Excel 2013 Pro 64 bit.
• Last modified: , [Australian Eastern Time (AET)]