How to Format a Date Time in Excel?

Introduction

In this article you will know about how to format a date and time in Excel. Date and time are very important entities in work life. Whether you are an business owner, a project manager or an individual employee, date and time plays important role in growth. Your deadlines are defined based on time and date. In such cases using Excel is the good idea. Date time format in Excel provides multiple formatting options which allows you to display date and time in different styles. In this guide you will know more about Excel date time formats and benefits of formatting them with Excel.

What are Date and Time Formats in Excel?

Date and time formats in Excel means how your data shown in cells. Excel stores dates as serial numbers and times as fractions of a day, then displays them based on the chosen format. You can use built-in formats like MM/DD/YYYY, DD-MMM-YYYY or HH:MM AM/PM, or create custom formats according to your needs. These formats make it easier to calculate date and time values in your spreadsheets.

There is a standard date time format called the ISO 8601 format, that is followed universally. To know more about it read the article ISO 8601 date time format and how to alter the date time format in Google Sheets.  

Why Excel Date Time Format is Important?

Excel stores date and time as numeric values. Following are the reasons why it's important to format date and time in Excel.

• Formatting dates will help you to display dates in readable formats. For example - 05-Jun-2025, June 5, 2025.
• Formatted time will be shown in 12-hour or 24-hour formats as per your convenience.
• You can also combine date and time. For example - 06/05/2025 14:30
• Formatting helps you to ensure consistency across different regional date and time formats. For example - US vs. international formats.
• You can have clean data for dashboards, reports and exports after date time formatting in Excel.
• When data is formatted, it is easier to use in functions, charts and pivot tables.
• Improper formatting can lead to errors in calculation and visual presentation.

How to change a Date Time format?

1. Click on the Home tab in the number group. Click on the Dialogue Box Launcher which is next to the number. Other way to open dialogue box launcher by pressing CTRL + 1 or the right click button.
2. Then in the category box, click on the Date or Time. Click on the format that closely resembles the date and time format you want. It is easier to start from a pre-existing format.  
3. Now, in the category box, you need to click on Custom. Then you need to click the type box and then make the changes that you want in the customized format. 

Now, you have changed the default format to the custom format. The entire procedure of changing the date and time format is complete.

Custom Excel DateTime Formats

You can create your custom date time formats to meet your needs. The formats improve readability.

🔹 Common Custom Excel Date Formats:

🔹 Common Time Formats:

🔹 Combine Date and Time:

Benefits of Formatting Date and Time in Excel

• Formatted date and time will make your data look more clear and understandable.
• Same date and time formats will maintain a professional look through out your Excel data.
• Formatted dates and times ensure that calculation are correct since formulas are based on correct date and time format.
• You can easily sort, filter and analyze date time based data due to uniform formatting.
• You can customize your date and time formats in Excel to display information exactly how you want it to be.

Fixing Common Date Time Formatting Issues in Excel

1: Date/Time is Shown as #####

• Cause: When column size is too narrow then full date and time values will be replaced with hash symbols by Excel.
• Fix: This can be fixed by Increasing the column width or by reducing the font size. It will make the full date time value visible.
  
  

2: Excel Considers Date Time as Text

• Cause: When data is imported from CSV or text files, date and time will be treated as plain texts.
• Fix: To fix this you can convert the text value to valid date or time by using =DATEVALUE() or =TIMEVALUE() functions.
  
  

3: Wrong Regional Date Time Format

• Cause: Excel may use different date format then your system format. It causes regional format issue.
• Fix: To fix this, go to File > Options > Regional Settings and set your date/time locale according to your region.
  
  

Advanced Use: Format Date Time with Formulas

You can make use of the TEXT() function to convert date/time into readable text with a custom format.

=TEXT(A1, "dddd, mmmm dd, yyyy hh:mm AM/PM")

The output of the above format will be Thursday, June 05, 2025 02:30 PM.

FAQs About Date Time Format in Excel

• Q1. Is it possible to format both date and time in one Excel cell?
• A: Yes, you can format both date and time in single cell using a custom date time format like dd/mm/yyyy hh:mm AM/PM.
  
  
• Q2. Can I change the default date format in Excel?
• A: Yes. Change your system’s regional settings or set a workbook-level format.
  
  
• Q3. What is the difference between short and long date format in Excel?
• A: A short date is numeric value like 6/5/2025 and a long date includes day and month names in text like Thursday, June 5, 2025.
  
  
• Q4. Why Excel does not recognize the date in a cell?
• A: The reason is, date is in the text format due to CSV or text file input. You can convert it into a valid date format using =DATEVALUE() function or reformat it to Text to Columns.

Conclusion

Excel date time formatting is easy when you know how to format. Making your date and time formats looks valid in Excel does not only improve readability but also help in accurate calculations. By applying simple date and time formats in Excel, you can customize your date time formats. Formatting date and time in Excel is an essential step towards keeping your data clear, accurate and professional. By applying the right date and time formats, you ensure proper calculations, make your data easier to read and enhance your overall data analysis.

If you're looking to automate workflows on date-based triggers or time-sensitive actions in Google Workspace, check out our automation tool xFanatical Foresight. It's a no-code automation platform designed to streamline repetitive tasks, helping teams save time and boost productivity. From organizing emails to scheduling Google Calendar events automatically, xFanatical Foresight does it all.