excel date time format not working

Excel Date Time Format Failing: Troubleshoot & Fix Now

By Mark Harris
AD

Are you pulling your hair out trying to format dates and times in Excel, but nothing seems to work? You're not alone. Date and time formatting in Excel can be a tricky business, but don't worry, we've got you covered. Let's dive into the common issues and their solutions.

Before we start, ensure you've selected the appropriate cell and that Excel recognizes the data as a date or time. If not, click on the cell, go to the 'Home' tab, click 'Format as Table' in the 'Styles' group, and check 'My table has headers' if it does. Then, click on 'Date' or 'Time' in the 'Number' group, depending on your data.

Excel Date Formatting Issues

Dates not displaying correctly? Let's tackle some common problems.

AD
Everything you need to know about working with dates and time in Excel
Everything you need to know about working with dates and time in Excel
How to Create a Timestamp in Excel
How to Create a Timestamp in Excel
the excel time and date sheet
the excel time and date sheet

For instance, if Excel shows dates as "ignement" or "m/d/yyyy h:mm", it could be due to regional settings. Here's how to fix it:

Incorrect Regional Settings

Excel uses your system's regional settings to display dates. If they're incorrect, dates will format wrongly. Here's how to change them:

[Fixed] Excel Date Filter Not Working - ExcelDemy
[Fixed] Excel Date Filter Not Working - ExcelDemy
[Solved!] Formulas to Compare Dates in Excel Not Working - ExcelDemy
[Solved!] Formulas to Compare Dates in Excel Not Working - ExcelDemy
a yellow sign that says how to fix excel dates that won't change format
a yellow sign that says how to fix excel dates that won't change format

1. Go to Control Panel > Clock, Language, and Region > Region and Language. 2. Click on 'Add New Language' and select your preferred language. 3. Under 'Default language for non-Unicode programs', select your preferred language again, then click 'OK'.

Incompatible Date Formats

If you're importing dates from another source, they might have incompatible date formats. To fix this, use the 'Text to Columns' feature:

AD

1. Select the data. 2. Go to 'Data' tab > 'Split Text to Columns' in the 'Data Tools' group. 3. Choose 'Delimited', then click 'Next'. 4. Uncheck all delimiters except the one used in your data, then click 'Next'. 5. Choose the column data type (Date, Time, or both), then click 'Finish'.

How to Convert Text Date and Time to Date Format in Excel - 7 Methods - ExcelDemy
How to Convert Text Date and Time to Date Format in Excel - 7 Methods - ExcelDemy
How to insert calendar in Excel (Date Picker & printable calendar template)
How to insert calendar in Excel (Date Picker & printable calendar template)
How to change date format in Excel 365 Online
How to change date format in Excel 365 Online
How to Calculate Time in Between Two Dates in Excel Fast
How to Calculate Time in Between Two Dates in Excel Fast
How to Remove Time from Date in a Pivot Table in Excel (A Step by Step Method) - ExcelDemy
How to Remove Time from Date in a Pivot Table in Excel (A Step by Step Method) - ExcelDemy
Free Excel Leave Tracker Template (Updated for 2026)
Free Excel Leave Tracker Template (Updated for 2026)

Excel Time Formatting Issues

Having trouble formatting times? Let's look at a common problem and its solution.

If Excel displays times without AM/PM, it might be because your cell format is incorrect.

AD

Incorrect Cell Format

To add AM/PM to your times:

Remove Time from Date and Time in Excel
Remove Time from Date and Time in Excel

1. Select the cells containing the times. 2. Right-click and select 'Format Cells' > 'Number' tab. 3. Under 'Category', select 'Time', then 'Custom'. 4. Enter "h:mm AM/PM" in the 'Type' field, then click 'OK'.

Leading Zeros Missing from Hours

If there are no leading zeros for hours less than 10, you can add them using the 'Number Format' feature:

AD

1. Select the cells containing the times. 2. Right-click and select 'Format Cells' > 'Number' tab. 3. Under 'Category', select 'Time', then 'Custom'. 4. Enter "00:00" in the 'Type' field, then click 'OK'.

And there you have it! With a bit of tweaking, you should have your dates and times displaying perfectly in Excel. If you're still having trouble, consider using the 'AutoFormat' feature to let Excel guess the format for you. Happy formatting!

AD