How to Format Dates and Times in Excel

Dates and times are among the most commonly used data types in Microsoft Excel. Whether you are tracking project deadlines, analyzing sales trends over time, managing employee attendance, or organizing schedules, proper date and time formatting is essential. Incorrect formatting can lead to calculation errors, misinterpretation of data, and confusion when sharing files with others.

This article provides a comprehensive guide on how to format dates and times in Excel. You will learn how Excel stores date and time values, how to apply built-in formats, how to customize formats, and how to troubleshoot common problems. By the end, you will have full control over how dates and times appear and behave in your worksheets.

Understanding How Excel Handles Dates and Times

Before formatting dates and times, it is important to understand how Excel stores them internally.

Date Values in Excel

Excel stores dates as serial numbers, starting from January 1, 1900 (in Windows-based systems). For example:

• January 1, 1900 = 1
• January 2, 1900 = 2
• January 1, 2025 = a much larger number

This system allows Excel to perform calculations such as subtracting dates to find the number of days between them.

Time Values in Excel

Time values are stored as fractions of a day:

• 12:00 PM = 0.5
• 6:00 AM = 0.25
• 6:00 PM = 0.75

Because dates and times are numerical values, Excel can add, subtract, and compare them—provided they are formatted correctly.

Applying Built-In Date and Time Formats

Excel includes several predefined date and time formats that you can apply quickly.

Using the Number Format Dropdown

1. Select the cells containing dates or times.

2. Go to the Home tab.

3. In the Number group, open the Number Format dropdown.

4. Choose:

   • Short Date
   • Long Date
   • Time

These options are useful for quick formatting without opening advanced settings.

Formatting Dates Using the Format Cells Dialog

For more control, the Format Cells dialog offers a wide range of options.

Steps to Access Format Cells

1. Select the cells you want to format.
2. Right-click and choose Format Cells, or press Ctrl + 1.
3. Click the Number tab.
4. Select Date from the category list.

Choosing a Date Style

Excel displays multiple date formats, such as:

• 3/14/2025
• 14-Mar-2025
• Friday, March 14, 2025

You can preview each format before applying it. Excel automatically adapts date formats based on your system’s regional settings.

Formatting Time Values in Excel

Time formatting works similarly to date formatting.

Applying Time Formats

1. Open the Format Cells dialog.

2. Select Time from the category list.

3. Choose from formats such as:

   • 1:30 PM
   • 13:30
   • 1:30:00 PM

These formats control whether seconds appear and whether the clock uses 12-hour or 24-hour notation.

Using Custom Date and Time Formats

Built-in formats may not always meet your needs. Custom formats allow complete control over how dates and times appear.

Accessing Custom Formats

1. Open the Format Cells dialog.
2. Select Custom under the Number tab.
3. Enter a format code.

Common Date Format Codes

• dd – Day with leading zero (01–31)
• d – Day without leading zero
• mm – Month number with leading zero
• mmm – Abbreviated month name (Jan)
• mmmm – Full month name (January)
• yy – Two-digit year
• yyyy – Four-digit year

Example: dd-mmm-yyyy → 05-Feb-2025

Common Time Format Codes

Time formatting also relies on specific symbols:

• hh – Hours with leading zero
• h – Hours without leading zero
• mm – Minutes
• ss – Seconds
• AM/PM – 12-hour clock indicator

Example: hh:mm:ss AM/PM → 09:45:30 PM

Formatting Combined Date and Time Values

Excel often stores dates and times together in a single cell.

Example Custom Formats

• dd/mm/yyyy hh:mm → 14/03/2025 09:30
• mmmm d, yyyy h:mm AM/PM → March 14, 2025 9:30 AM

These formats are useful for logs, timestamps, and scheduling data.

Changing Regional Date and Time Formats

Excel’s default formats depend on your system’s regional settings.

How Regional Settings Affect Dates

• United States: MM/DD/YYYY
• Europe and many other regions: DD/MM/YYYY

When sharing files internationally, date interpretation can become ambiguous.

Adjusting Excel’s Regional Behavior

While Excel primarily follows system settings, you can control display using custom formats to ensure clarity, regardless of region.

Formatting Dates Entered as Text

A common issue occurs when dates are entered as text instead of recognized date values.

Identifying Text Dates

• Dates align to the left by default.
• Functions like TODAY() do not work correctly.
• Sorting behaves incorrectly.

Converting Text to Dates

Method 1: Text to Columns

1. Select the cells.
2. Go to Data > Text to Columns.
3. Choose Date and select the correct format.
4. Finish the wizard.

Method 2: DATEVALUE Function Use:

=DATEVALUE(A1)

Then apply a date format.

Formatting Time Durations (Hours Exceeding 24)

Standard time formats reset after 24 hours, which can cause issues when tracking durations.

Example Problem

• 27 hours displays as 3:00 AM instead of 27:00.

Solution: Custom Duration Format

Use:

[h]:mm

This format allows Excel to display cumulative hours correctly, making it ideal for timesheets and project tracking.

Using Date and Time Formatting with Formulas

Formatting affects appearance, not the underlying value.

Example

If cell A1 contains:

=TODAY()

You can apply any date format without changing the actual value.

Similarly, time formulas like:

=NOW()

can be formatted as date-only, time-only, or both.

Common Formatting Mistakes and How to Fix Them

Dates Display as Numbers

Cause: Cell is formatted as General or Number. Fix: Apply a Date format.

Incorrect Month and Day Order

Cause: Regional format mismatch. Fix: Use custom formats like yyyy-mm-dd.

Times Not Calculating Correctly

Cause: Time stored as text. Fix: Convert to numeric time and reformat.

Best Practices for Formatting Dates and Times

• Always confirm Excel recognizes dates as numeric values
• Use ISO format (yyyy-mm-dd) for international files
• Apply duration formats for elapsed time
• Avoid manual typing of date formats when possible
• Standardize formatting before sharing workbooks

When Formatting Matters Most

Correct date and time formatting is especially critical when:

• Creating dashboards
• Using pivot tables
• Performing time-based calculations
• Importing data from external systems
• Automating reports with formulas or macros

Inconsistent formatting can lead to errors that are difficult to detect.

Conclusion

Formatting dates and times in Excel is a foundational skill that significantly impacts data accuracy, readability, and usability. While Excel’s default formats are sufficient for basic tasks, understanding custom formats and internal date logic unlocks far greater control and precision.

By mastering date and time formatting, you ensure your worksheets remain reliable, professional, and easy to interpret—whether for personal use, business reporting, or collaborative projects. With the techniques covered in this guide, you are well equipped to handle any date or time formatting challenge in Excel.