• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Date and Time Formatting

You are here: Home / Excel / Excel Date and Time Formatting
Excel Date and Time Formatting
October 20, 2017 by Mynda Treacy

Even though dates and time are actually stored as a regular number known as the date serial number, we can make use of extensive Excel date and time formatting options to display them just the way we want.

We can access some quick date and time formats from the Home tab > in the Number group:

Excel Date and Time Formatting built in

Custom Excel Date and Time Formatting

We can also create our own custom date and time formats to suit our needs. Let's take a look.

  1. Select the cell(s) containing the dates you want to format.
  2. Press CTRL+1, or right-click > Format Cells to open the Format Cells dialog box.
  3. On the Number tab select 'Date' in the Categories list. This brings up a list of default date formats you can select from in the 'Type' list. Likewise for the Time category.

Excel Date and Time Format cells

We aren't limited to the defaults though. You can create your own Custom date or time formats in the 'Custom' category. These custom formats are saved for you to re-use in the current file.

Excel Date and Time custom Format cells

Custom Date Formatting Characters

Excel recognises the following characters and sets of characters for date formatting.

Character Explanation   Date Formatted
d Displays the day as a number without a leading zero. 3/09/2016 3
dd Displays the day as a number with a leading zero when appropriate. 3/09/2016 03
ddd Displays the day as an abbreviation (Sun to Sat). 3/09/2016 Sat
dddd Displays the day as a full name (Sunday to Saturday). 3/09/2016 Saturday
m Displays the month as a number without a leading zero. 3/09/2016 9
mm Displays the month as a number with a leading zero when appropriate. 3/09/2016 09
mmm Displays the month as an abbreviation (Jan to Dec). 3/09/2016 Sep
mmmm Displays the month as a full name (January to December). 3/09/2016 September
mmmmm Displays the month as a single letter (J to D). 3/09/2016 S
yy Displays the year as a two-digit number. 3/09/2016 16
yyyy Displays the year as a four-digit number. 3/09/2016 2016
 

Custom Date Formatting Examples

We can bring the characters together to create our own custom formats. Some examples below:

Excel Date Formatting example

Remember; the custom format doesn't alter the underlying date serial number, it is still the same.

Custom Time Formatting Characters

Like dates, time also has its own set of custom formatting characters, as listed below:

Character Explanation    
h Displays the hour as a number without a leading zero.
[h] Displays elapsed time in hours. If you are working with a formula that returns a time in which the number of hours exceeds 24, use a number format that resembles [h]:mm:ss or [h]:mm
hh Displays the hour as a number with a leading zero when appropriate. If the format contains AM or PM, the hour is based on the 12-hour clock. Otherwise, the hour is based on the 24-hour clock.
m Displays the minute as a number without a leading zero.*
[m] Displays elapsed time in minutes. If you are working with a formula that returns a time in which the number of minutes exceeds 60, use a number format that resembles [mm]:ss.
mm Displays the minute as a number with a leading zero when appropriate.*
s Displays the second as a number without a leading zero.
[s] Displays elapsed time in seconds. If you are working with a formula that returns a time in which the number of seconds exceeds 60, use a number format that resembles [ss].
ss Displays the second as a number with a leading zero when appropriate. If you want to display fractions of a second, use a number format that resembles h:mm:ss.00.
AM/PM, am/pm, A/P, a/p Displays the hour using a 12-hour clock. Excel displays AM, am, A, or a for times from midnight until noon and PM, pm, P, or p for times from noon until midnight.
 

*Note: The m or mm code must appear immediately after the h or hh code or immediately before the ss code; otherwise, Excel displays the month instead of minutes.

Custom Time Formatting Examples

Excel Time Formatting example

Note: if your PC region settings have the Date & Time formats set to show the Short Time as hh:mm tt or the Long Time as hh:mm:ss tt then this may override any single 'h' formats and display them as 'hh'.

The screenshot above is what I see with my PC region settings for the Short Time as h:mm tt. If you see something different when using a single ‘h’ format, then it will be down to your PC region settings.

More Excel Formatting

Custom cell formatting isn't limited to dates and times. There is a plethora of formatting options for all types of numbers that we can use to get our reports looking just the way we want. Click here for our comprehensive guide to Excel custom number formatting.

Free eBook - Working with Date & Time in Excel

Everything you need to know about Date and Time in Excel - Download the free eBook and Excel file with detailed instructions.

Enter your email address below to download the comprehensive Excel workbook and PDF.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook and PDF. Note: This is a zip file including an Excel workbook with detailed instructions and a PDF version for your reference.

Excel Date and Time Formatting

More Excel Posts

excel templates

Where to Find Free Excel Templates

Where to find free Excel templates and how to create your own Excel templates. Using templates saves time and effort.
Easily Remove Password Protection from Excel Files

Easily Remove Excel Password Protection

How to remove Excel password protection when you’ve forgotten the password. Works for sheets, workbooks and read only files.
Import data from a picture to Excel

Import Data from a Picture to Excel

Import data from a picture to Excel. Works with pictures from a file or the clipboard and loads it to the spreadsheet.
excel online

5 Excel Online Features Better than Desktop

5 Excel Online Features Better than Desktop including searchable data validation, track changes, single line ribbon and more.

10 Common Excel Mistakes to Avoid

10 common Excel mistakes to avoid, including merge cells, external links, formatting entire rows/columns and more.
new Excel features

Cool New Features in Excel for Microsoft 365

Cool New Features in Excel for Microsoft 365 including the navigation pane, smooth scroling, unhide multiple sheets and more.
dynamic dependent data validation

Dynamic Dependent Data Validation

Dynamic Dependent Data Validation with dynamic array formulas like FILTER make it quick and easy to set up.
QAT

Excel Quick Access Toolbar

The Excel Quick Access Toolbar is not only a handy for your mouse, but it also enables some super easy keyboard shortcuts.

Share and Collaborate in Excel

Share and Collaborate in Excel just like Google Sheets! Show changes, custom views, threaded comments with @ mentions and more.
Workbook Protection

Excel Workbook Protection

Excel Workbook protection can prevent your users from breaking your reports while still allowing interaction with Slicers and refreshing.
Category: Excel
Previous Post:Excel NETWORKDAYS.INTL Function
Next Post:Fix Excel Dates Formatted as TextFix Excel dates formatted as text

Reader Interactions

Comments

  1. Josh N

    January 25, 2022 at 5:05 am

    Below is my current battle…

    Sample cell – 11/26/2021 1:24pm
    Custom format – m/d/yyyy h:mm am/pm

    After entering the custom format for the column, I click sort and I am still getting AM times mixed into my PM times…
    11/26/2021 1:55pm
    11/26/2021 1:57pm
    11/26/2021 10:14am
    11/26/2021 10:18pm
    11/26/2021 10:26am
    11/26/2021 10:32am
    11/26/2021 10:33pm
    11/26/2021 10:36pm
    11/26/2021 10:38am

    What am I missing here?!

    Reply
    • Philip Treacy

      January 25, 2022 at 8:34 am

      Hi Josh,

      Hard to be sure without seeing your data but I’m guessing your data is actually text, not date/time. Press CTRL + ` and you should see the serial numbers for the dates/times. If you don’t, it’s text and you need to convert it to date/time. Try using Power Query or refer to this https://www.myonlinetraininghub.com/fixing-excel-dates-formatted-text

      Regards

      Phil

      Reply
  2. Jorge Herrera

    September 19, 2020 at 2:51 am

    [$-x-systime]h:mm:ss AM/PM

    Can u explain this formula?

    Reply
    • Mynda Treacy

      September 19, 2020 at 6:14 pm

      Hi Jorge,

      It’s a custom number format. The [$-x-systime] is a culture tag specifying the system time format, as explained here.

      Mynda

      Reply
  3. Gael

    August 14, 2020 at 11:07 pm

    Can I add times? If I enter start and end times, is there a formula that will translate that into minutes spent?

    Reply
    • Mynda Treacy

      August 15, 2020 at 1:31 pm

      Hi Gael,

      If you want to show the cumulative time in minutes then you simply use a custom number format: [m]:ss

      Mynda

      Reply
      • Gael

        August 21, 2020 at 10:28 pm

        Thanks, Mynda, I appreciate you taking the time to answer, but I have no idea how to translate what you’ve told me into a formula in my spreadsheet. If you could speak slowly (all I know about Excel I’ve picked up on my own; I’m absolutely just winging it) or provide a link to an example?

        Reply
        • Mynda Treacy

          August 22, 2020 at 9:32 am

          In this tutorial under the heading Custom Excel Date and Time Formatting I cover the steps for putting that format in your worksheet. If you’re still stuck, please post your question and Excel file on our forum where we can help you further.

          Reply
  4. Geoff

    March 24, 2020 at 10:53 pm

    I’ve downloaded a csv file that has a time on it but when its imported just looks like a number ie 10:46 ,if I open the CSV in Excel and use a formula on it it shows as the decimal equivalent of the number is no longer the same time but rather a decimal representation of it and doesn’t hold the 10:46 i require ,this isn’t then comparable with other sheets that have the correct time format from elsewhere

    I cannot get to change to a time format that equals the original downloaded number/time
    Thank you

    Reply
    • Geoff

      March 25, 2020 at 1:43 am

      someone has kindly solved this for me on another site thank you

      Reply
      • Mynda Treacy

        March 25, 2020 at 8:59 am

        Glad you found a solution, Geoff.

        Reply
  5. Pat

    January 15, 2020 at 1:37 am

    I can’t find any way to put a date and time with am, pm into one cell, without it being a timestamp.
    I am creating a spreadsheet for keeping track of medicines I take. (I have a crazy dr that makes me bring all my meds in every appointment and have them counted.) So take some meds at wake-up, lunch and bedtime. I currently have a cell for the date of the last appointment and the date of the next appointment with another cell calculating the number of days between the two. I was wondering if there is a way I could put the time and date of each appointment in the cells and somehow create a formula that would count how many pills I took and had left according not just the days between appointments but somehow configuring the time of the appointments. I hope I am describing this correctly. Thanks!!!

    Reply
    • Catalin Bombea

      January 15, 2020 at 3:07 am

      Hi Pat,
      Can you please upload your sample file on our forum? We will be able there to help you with a personalized solution using your file.

      Reply
  6. yehia abo elnaga

    November 20, 2019 at 10:47 pm

    Please i am looking how to extract the number day of any date ( year days 365)

    for example ( today is 20/11/2019 ) = (number day is (323) from 365 day)

    thanks

    Reply
    • Catalin Bombea

      November 21, 2019 at 12:13 am

      Try:
      =A1-DATE(YEAR(A1),1,1)

      Reply
      • yehia abo elnaga

        November 23, 2019 at 9:25 pm

        thanks for your support
        it’s (Done)

        but if i need the reverse of the equation
        for example ( number day is 327) = ( so the result is the date ( 23/11/2019 ) ).

        Reply
        • Catalin Bombea

          November 24, 2019 at 1:06 pm

          That should be obvious, in excel 1 day equals one unit. If you add to 01/01/2019 date the 327 number, that’s all.
          If you have the date in a cell, a basic addition is enough: =A1+327

          Reply
          • yehia abo elnaga

            November 24, 2019 at 6:38 pm

            Thank you very much

  7. Aldas Baltutis

    November 20, 2019 at 12:43 am

    i am looking for the elegant way to calculate night and day power consumption. the problem is that night tariff is calculated from 23:00 to 7:00, and counter indications are not taken daily.
    e.g.
    26 October, 13:06
    28 October, 11:26
    4 November, 17:46
    how to calculate the time (hours and minutes) that can be attributed to the night/day time?

    Reply
    • Catalin Bombea

      November 20, 2019 at 12:58 am

      Is it enough if you multiply the number of days by 8?
      =CEILING(a2-a1,1)*8
      I assume you have dates in a1 and A2, not text (26 October, 13:06 is missing the year).

      Reply
  8. Daniel A DePasquale

    November 13, 2019 at 7:49 am

    when downloading information to upload into my accounting software the date is displayed as 01/15/2019 Wed. How can i get rid of the text without doing it individually? I’ve tried to play with the date formatting but haven’t been successful.

    Reply
    • Mynda Treacy

      November 13, 2019 at 8:50 am

      Hi Daniel,

      You can use Power Query to split the column into the Date and week day name, or you can use Text to Columns.

      Mynda

      Reply
  9. Sherman

    June 14, 2019 at 5:28 am

    What if you don’t want your output in military (24-hour time)? I’m doing a value of how many times something is mentioned and converting that value into 15 minute increments. So for example, if something was mentioned twice, that would convert to a half an hour aka 30 minutes.

    I want the display to just display 0:30 to reflect 30 minutes. Or if something is mentioned 4 times, the output should be 1:00 or one hour.

    I don’t need an actual time of day, just a time value to represent the sum of time. Any help here is appreciated.

    Reply
    • Mynda Treacy

      June 16, 2019 at 11:19 am

      Hi Sherman,

      You can apply this number format:

      [h]:mm

      Mynda

      Reply
  10. Francisco

    May 30, 2019 at 1:57 am

    I have a doubt related to how time format and the data input formatting can be locked in excel.
    An excel time schedule need to be filled by a team in a certain cells i.e. Start and End columns and the time by lines [12:00] [13:00].
    Although, the cells are formatting [hh:mm] the team not comply with this format and it is usual to have data inputted as 12h30m, 12.30, 12,30 which retrieves a lot of errors in the linked tables.
    How can I avoid the wrong format data input by the team in the time schedule sheet, can I lock the format in that cells in the way to retrieve an error or not allows to input data unless it is with the right format, something like a drop box in data validation excel feature?
    Many thanks.

    Reply
    • Mynda Treacy

      May 30, 2019 at 7:54 am

      Hi Francisco,

      You can use Data Validation to force users to input values in a specific format: https://www.myonlinetraininghub.com/excel-drop-down-lists

      Mynda

      Reply
  11. OMAR ALSHARIF

    March 14, 2019 at 6:39 am

    so many thanks for this effort.
    You are so amazing.
    I noticed that in the Date & Time 101 work sheet, there is a typing mistake.
    You wrote: (24 hours or half of a day, and 1 hour is 0.41666′ because it’s 1/24 of a day.)
    It is 0.041666 not 0.41666
    Kindly correct this also in the PDF.

    Regards.

    Reply
    • Mynda Treacy

      March 14, 2019 at 10:10 am

      Thanks, Omar. Well spotted. I’ve made those amendments.

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Shopping Cart

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now
  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
 
  • About My Online Training Hub
  • Contact
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x