Excel Date Formats Explained: DD-MM vs MM-DD, Locale, Format Cells (Ctrl+1) and Custom Lists

By Ravindra Bagale · Last updated: 10 October 2026

Practice file: excel-dates-format-cells-practice.xlsx has every example from this chapter, plus "Your turn" areas.

1. Typing a date Excel understands

Why: a real date can be sorted, filtered and used in calculations (days between two dates, month-wise totals). A date that Excel did not recognise is only text, and none of that works.

How: with Indian date settings, type any of these in a cell and press Enter:

1. 1-8-26 (with dashes)
2. 1/8/26 (with slashes)
3. 01-08-2026

Excel shows 01-08-2026 for all three. Two quick checks tell you it is a real date:

1. The value sits on the right side of the cell. Numbers and dates align right; text aligns left.
2. Drag the fill handle down: you get 02-08-2026, 03-08-2026 … one day per cell.

Use the same year everywhere in a sheet. Typing 26 gives 2026; mixing 20 and 26 by mistake gives dates six years apart.

2. Dragging dates with a touchpad

Why: the fill handle is a tiny square, and many laptops have no mouse attached.

How (two-finger method):

1. Click the cell with the date.
2. Move the pointer onto the fill handle (bottom-right corner) until it becomes a thin black +.
3. Press and hold the touchpad click with one finger.
4. With a second finger, slide down the touchpad. The selection grows as you slide.
5. Lift both fingers. The dates are filled.

3. DD-MM vs MM-DD: why 1-8-26 can mean two dates

What: India and the UK write the day first (DD-MM-YYYY). The United States writes the month first (MM-DD-YYYY). Excel follows the regional format (locale) of your computer when it reads what you type.

How to spot a US setting quickly:

1. Type 13-8-26. If it stays on the left side of the cell, Excel did not accept 13 as a month, so it reads dates month-first.
2. Type 1-8-26 and drag it down. If you see 1-9-26, 1-10-26 … the middle number is growing. To an Indian eye that looks like the month changing, but under a US setting the middle number is the day.

How to check or change the setting (Windows):

1. Open Settings › Time & language › Language & region (on Windows 10: Time & Language › Region).
2. Under Regional format, choose English (India) for DD-MM-YYYY. English (United States) gives month-first dates.
3. Close Excel and open it again so it picks up the new setting.

4. How Excel really stores a date

What: inside Excel, a date is a number: the count of days starting from 1 January 1900. 1 August 2026 is stored as 46235. The DD-MM-YYYY you see is only a format on top of that number.

Try it:

1. Type 1-8-26 in a cell.
2. Press Ctrl+1, choose General, and click OK. The cell shows 46235.
3. Press Ctrl+Z. The date look comes back.

Because a date is a number, =B2-A2 gives the days between two dates, and dragging a date adds 1 each time.

5. Format Cells (Ctrl+1)

Why: the same value can be shown in many ways: 1234.5, 1,234.50, ₹ 1,234.50 or 01-08-2026. Format Cells changes the look, never the value.

How:

1. Select the cell or the whole column (click the column letter).
2. Press Ctrl+1 (on a Mac, Cmd+1), or right-click › Format Cells.
3. Stay on the Number tab. On the left is the Category list.

Currency vs Accounting: both show the ₹ sign. Accounting lines up the ₹ signs and the decimal points in a column, and shows negative amounts in brackets, like (250.00). That is why accountants like it for ledgers.

6. Change how a date looks: Locale and Type

How:

1. Select the date column.
2. Press Ctrl+1 › Date.
3. In Locale (location), choose English (India).
4. In Type, pick a style. Excel previews each style with a sample date, 14 March 2012, so 14-03-2012 means day-month-year.
5. Watch the Sample box, then click OK.

The same date, 1 August 2026, in different types:

7. Mixed date looks are a cleaning job

Why: when dates come from different people or systems, one column may show 1-8-26, 02 August 2026 and 03.08.26 together. That is hard to read and easy to misread. Making one consistent look is part of data cleaning, the second of the five steps of data analytics.

How:

1. Select the whole date column.
2. Ctrl+1 › Date › Locale English (India) › Type 14-03-2012.
3. Click OK. Every real date now shows as DD-MM-YYYY.

Format Cells works only on real dates (right-aligned). A date that is stored as text (left-aligned) does not change, because for Excel it is just letters. Those need to be converted first; the lesson Text Dates and Mixed Date Formats shows how.

8. AutoFill takes the step you give it

AutoFill does not know what you "meant"; it reads the step from the cells you select.

1. Type 99 and 91, select both and drag down.
2. You get 83, 75, 67 … The step is 91 − 99 = −8, so the series goes down by 8.

If you get an odd series, look at the two seed cells first. A typing slip there is copied into every filled cell.

9. Custom Lists: teach Excel your own series

Why: Excel already knows some lists: type Monday or Jan and drag, and the rest follows. A Custom List lets you add your own: staff names, branch cities, product groups or anything you type again and again in the same order.

How to create one from cells (Windows):

1. Type the list in one column, one item per cell, for example the ten names Ravindra, Shraddha, Ruhi, Shahrukh, Amir, Salman, Zoya, Ravina, Raja, Rani in A5:A14.
2. Click File › Options › Advanced.
3. Scroll right to the bottom, to the General section, and click Edit Custom Lists….
4. Click inside Import list from cells and select A5:A14 on the sheet. The box shows $A$5:$A$14.
5. Click Import. The names appear in the List entries box.
6. Click OK, then OK again.

On a Mac, the same dialog is under Excel › Preferences › Custom Lists.

Use it:

1. In any empty cell, type Salman.
2. Drag the fill handle down ten cells.
3. You get Salman, Zoya, Ravina, Raja, Rani, and then the list wraps around: Ravindra, Shraddha, Ruhi, Shahrukh, Amir.

You can start from any name in the list, and the list keeps the order you typed.

Where is the list saved? In Excel on that computer, not inside the workbook. Open the same file on another laptop and the drag gives Salman, Salman, Salman until you create the list there too. Excel for the web is free to use but has fewer features, and Custom Lists is one of the missing ones.

10. The whole chapter on one page

1. A real date is right-aligned and drags one day per cell. Text stays left-aligned.
2. Day-first (India, UK) vs month-first (US) comes from the computer's regional format. Test with 13-8-26.
3. Change it in Settings › Time & language › Language & region › Regional format, then restart Excel.
4. A date is stored as a number: 1 August 2026 = 46235.
5. Ctrl+1 opens Format Cells: General, Number, Currency, Accounting (brackets for negatives), Percentage, Date, Text.
6. Date › Locale English (India) › Type 14-03-2012 gives DD-MM-YYYY.
7. One consistent date look is part of data cleaning; Format Cells works only on real dates.
8. AutoFill copies the step of the seed cells (99, 91 → −8).
9. File › Options › Advanced › Edit Custom Lists… › Import makes your own fill list; it lives on that computer.

11. Try at home

Recap

Quick revision cards

Englishमराठीहिंदी

Quick revision

Revise this chapter in two minutes. Save the cards on your phone or download them as PNG files. All names and numbers are made up for practice.

More cards for every chapter: Ravi Sir's Cards.