Excel time format: 12/24 hour, custom, default

This tutorial explains the basics and beyond of the Excel time format.

Microsoft Excel has a handful of time features and knowing them in depth can save you a lot of time. To leverage powerful time functions, it helps to know how Excel stores times. In this article, you will find everything you need to know about formatting time in Excel:

Excel time format

If you have been following our Excel Date Format tutorial, you know that Microsoft Excel stores dates as sequential numbers beginning with January 1, 1900, which is stored as number 1. As time is a portion of a day, times are stored as decimal fractions.

In Excel's internal system:

  • 00:00:00 is stored as 0.0
  • 23:59:59 is stored as 0.99999
  • 06:00 AM is 0.25
  • 12:00 PM is 0.5

When both date and time are entered in a cell, the value is stored as a decimal number comprised of an integer representing the date and a decimal portion representing the time. For example, 1 June 2025 9:30 AM is stored as 45809.39583. Time formatting in Excel

How to get a decimal number representing time in Excel

To get a decimal number representing a certain time, carry out these steps:

  1. Select a cell containing the time.
  2. Press Ctrl + 1 to open the Format Cells dialog box.
  3. On the Number tab, select General under Category, and you will see the decimal in the Sample box.

Now, you can write down that number and click Cancel to close the window. Or, you can click the OK button and have the time replaced with a corresponding decimal number in the cell. Get a decimal number representing the time in Excel.

To keep both the original time and its decimal representation, enter a simple formula like =B3 (where B3 is the time value) in any empty cell, and set the General number format for that cell. Return a decimal number representing the time in a separate cell.

This is the fastest way to convert time to decimal in Excel. You can also use different formulas to convert time to hours, minutes or seconds.

How to format time in Excel

Microsoft Excel is smart enough to recognize a time value as you type it in a cell. For example, if you type 20:30, or 8:30 PM, or even 8:30 p, Excel will interpret this as a time and display either 20:30 or 8:30 PM, depending on your default time format.

To change an existing time formatting or apply some specific one, use the Format Cells dialog as described below.

  1. Select the cell(s) that you want to format.
  2. Press Ctrl + 1 to open the Format Cells dialog.
  3. On the Number tab, select Time from the Category list, and then choose the desired format from the Type list.
  4. Click OK to apply the selected format and close the dialog box. Apply or change the time format in Excel.

Custom time format in Excel

Though Microsoft Excel provides a number of predefined time formats, you may want to create your own one that fits best for a particular sheet. This can also be done using the Format Cells dialog box:

  1. Select the target cells.
  2. Press Ctrl + 1 to open the Format Cells dialog.
  3. Under Category, select Custom and type the desired format code in the Type box.
  4. Review the Sample value to check if it's formatted as expected.
  5. Click OK to save the changes.

For example, to format a date time value like 1-Jun-2025 9:30 AM, utilize this code: d-mmm-yyyy h:mm AM/PM. Create a custom time format in Excel.

The custom time format you've created will be in the Type list the next time you need it.

Tip. The easiest way to make a custom time format is to use one of the existing formats as a starting point. For this, click Time in the Category list, and select one of the predefined formats under Type. After that switch to Custom and make the changes to the format displayed in the Type box.

Excel time formatting codes

When creating a custom time format in your worksheets, you can use the following codes.

Code Description Displays as
h Hours without a leading zero 0-23
hh Hours with a leading zero 00-23
m Minutes without a leading zero 0-59
mm Minutes with a leading zero 00-59
s Seconds without a leading zero 0-59
ss Seconds with a leading zero 00-59
AM/PM Periods of the day
(if omitted, 24-hour time format is used)		 AM or PM

12 hour time format in Excel

To set the 12 hour format for times in Excel, include AM/PM in the format code that you enter in the Format Cells dialog.

Format Displays as
h:mm:ss AM/PM 1:30:00 PM
h:mm AM/PM 1:30 PM

To change time to 12 hour format, you can also use the TEXT function with one of the codes listed above.

Assuming the original time value is in A3, the formula takes this form:

=TEXT(A3,"hh:mm:ss AM/PM") Change time to 12 hour format.

Note. The TIME function converts a time value into a text string. If you intend to calculate times at a later point, then set a custom 12-hour format using the Format Cells dialog.

24 hour time format in Excel

To apply the 24 hour time format, use any format code without AM/PM.

Format Displays as
h:mm:ss 13:30:00
h:mm 13:30

Once you've decided on the format code, apply a corresponding custom format to the original cell or supply the format code to the TEXT function to return a formatted time in another cell. Remember that in the latter case, the output will be a text string.

With the source time in A3, the formula goes as follows:

=TEXT(A3,"hh:mm:ss") Change time to 24 hour format.

Excel time format over 24 hours

When adding up times, the total may exceed 24 hours. To format times over 24 hours correctly, enclose the hour code in square brackets like [h].

Here are some examples of time formats over 24 hours:

Format Displays as Explanation
[h]:mm 41:30 41 hours and 30 minutes
[h]:mm:ss 41:30:10 41 hours, 30 minutes and 10 seconds
[h] "hours", mm "minutes", ss "seconds" 40 hours, 30 minutes, 10 seconds
d h:mm:ss 1 17:30:10 1 day, 17 hours, 30 minutes and 10 seconds
d "day" h:mm:ss 1 day 17:30:10
d "day," h "hours," m "minutes and" s "seconds" 1 day, 17 hours, 30 minutes and 10 seconds

For more information, please see how to show, add, subtract over 24 hours, 60 minutes, 60 seconds.

Excel date time format

To create custom formatting for date and time, use various combinations of time and date format codes.

The following table provides some examples of how your Excel date time formats may look like:

Format Displays as
d-mmm-yy h:mm:ss AM/PM 13-Jan-25 1:30:00 PM
mmmm dd, hh:mm AM/PM January 13, 01:30 PM
dddd, m/d/yy h:mm:ss Monday, 1/13/25 13:30:00
ddd, mmmm dd, yyyy hh:mm Mon, January 13, 2025 13:30

Excel time format without date

To format a date time value so that only time is visible in a cell, use only the time codes without the date codes. At that, you can use the international standard notation such as hh:mm:ss or your custom notation. Here are a few examples:

Format Displays as
h:mm:ss AM/PM 1:30:00 PM
hh:mm:ss AM/PM 01:30:00 PM
hh:mm:ss 13:30:00
hh-mm-ss 13-30-00
hh.mm.ss 13.30.00

How to format negative time values

The custom time formats discussed above work for positive values only. If the result of your calculations is a negative number formatted as time (e.g. when you subtract a bigger time from a smaller one), the result will be displayed as #####. If you want to format negative time values differently, the following options are available to you:

  • Display an empty cell for negative times. Type a semicolon at the end of the time format, for example [h]:mm;
  • Display an error message. Type a semicolon at the end of the time format, and then type a message in quotation marks, e.g. [h]:mm;"Negative time"

Generally speaking, a semicolon acts as a delimiter to separate positive values' format from negative values' formatting. For full details, please see Custom Excel number format.

If you want to display negative times as negative values, e.g. -10:30, the easiest way is to switch to Excel's 1904 date system. For this, click File > Options > Advanced, scroll down to When calculating this workbook section and check the Use 1904 date system box.

For more information, please see How to calculate and display negative times in Excel.

Excel default time format

When setting up a time format in the Format Cells dialog, you may have noticed that one of the formats begins with an asterisk (*). This is the default time format in your Excel. The default time format in Excel

To quickly apply the default Excel time format to the selected cell or a range of cells, go to the Home tab > Number group, and choose Time from the Number Format drop-down list. Apply the default time format in Excel

How to change the default time format

To change the default time format in Excel, this is what you need to do:

  1. Open the Control Panel and click Region. If your Control panel opens in Category view, then click Clock and Region > Change the date, time, or number format.
  2. In the Region dialog box, set the Short time and Long time formats you want. Change the default time format in Excel.

Now that you've got the hang of time formatting in Excel, it will be much easier for you to manipulate date and time values in your worksheets. I thank you for reading and hope to see you on our blog next week!

Practice workbook for download

Excel time formatting - examples (.xlsx file)

Table of contents

269 comments

  1. How do I add to this formula to start calculating time after a 24hr allotment.... I want to start calculating after the first 24hrs - HELP!!

    =IF(INT(B2-A2)>0, INT(B2-A2) & " days, ","") & IF(HOUR(B2-A2)>0, HOUR(B2-A2) & " hours, ","") & IF(MINUTE(B2-A2)>0, MINUTE(B2-A2) & " minutes and ","") & IF(SECOND(B2-A2)>0, SECOND(B2-A2) & " seconds","")

  2. how can you get the time elapsed in h:mm from to date & times. example( 12/30/2017 18:16 / 1/2/2018 4:39. What is the time elapsed between these two?

  3. DOWN_DATE DOWN_TIME CLEARED_DATE CLEARED_TIME
    15.12.2017 5:27:04 15.12.2017 8:18:38
    14.12.2017 18:34:24 14.12.2017 20:56:45
    30.12.2017 12:17:42 30.12.2017 13:28:11
    13.12.2017 7:11:34 14.12.2017 8:53:06
    15.12.2017 5:27:14 15.12.2017 8:18:12

    how to calculate diffrence of down duration .in excel 13.12.2017 7:11:34 -14.12.2017 8:53:06 showing as 1/1/1900 1:41:32 AM .which is not correct .tell me correct formula pl

    • Hello,

      If I understand your task correctly, please try the following formula:

      =ROUNDDOWN(B1-A1,0) & " days " & TEXT(TIME(HOUR(B1-A1),MINUTE(B1-A1),SECOND(B1-A1)), "hh:mm:ss")

      Where cell A1 is “13.12.2017 7:11:34”, cell B1 is “14.12.2017 8:53:06”

      Hope this will help you!

  4. Hi guys. I need help please...
    I need to calculate the number of lates and undertime using this details
    B2 - date of time in/out
    C2 - time in/out
    E2 - work schedule in date
    F2 - time schedule
    Thanks guys hope for you immediate response.

    • Hello,

      For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
      Please also don't forget to include the link to this comment into your email.

      I'll look into your task and try to help.

  5. Hi,I have a coloum with time format - 1 h 10 m 16 s
    How can i convert to show 24:00:00.
    plz guid

  6. Hi - I'm trying to evaluate a planned timetable against the actual journey times. Generally straightforward (a simple a-b gives the right answer) but if my journey time took less time than scheduled (ie I'm planned to arrive at 10:15 but arrived at 10:05) how can I automatically calculate this to show a response of -00:10?

    Your guidance as ever...

    • Hello, Tablespider,

      Please try the following formula:

      =IF(A1>=B1,A1-B1,"-"&HOUR(B1-A1)&IF(MINUTE(B1-A1)<10,":"&"0"&MINUTE(B1-A1),":"&MINUTE(B1-A1))&IF(SECOND(B1-A1)<10,IF(SECOND(B1-A1)=0,"",":"&"0"&SECOND(B1-A1)),":"&SECOND(B1-A1)))

      Hope it will help you.

  7. Hi,
    I have a column with Date and Time. I need to write a formula to show, if the date and time is 10-10-2017 12:53 PM, if time is after 2:30 PM the date to change to next date. Please advise.

    • Hi, Austin,

      could you please specify where is that "next date" located? Should the formula just return the next date from the list or do you have an additional list?

  8. 145:59:38 i have a question how do I covert it to seconds.

  9. How can get a formula result of time blocks in my column to display as: 9.30am-10.00am
    10.00am-10.30am
    10.30am-11.am

  10. Hello All,
    I exported a data from the timekeeping software which exported in the following format as 07/10/2017 06:58, but we need to see the output coming as 7/10/2017 6:58:00 AM. The problem is
    1- The format function is not working on the cell as its an exported data for some reason it doesnot apply to this.

    The format does change to 7/10/2017 6:58:00 AM automatically on ONLY WHEN I click inside the cell and hit ENTER, but then we have to do this for thousands of cells one by one.

    I forgot the formula to convert this to decimal, if someone can help me with that would be a great help

  11. Hi, I have in a formatted the cell to display a date and time (27/01/2017 15:04). I would like a formula to calculate/conditional format to work out if the time is between 07:00 and 00:00

  12. I simply want to display AM or PM in a cell based on the current computer time, no time or date.

    I have tried =IF(A2<0.5,"AM","PM") it will show AM but doesn't change for me when the computer time changes and I refresh the spreadsheet.

    I've also tried the =NOW() and a custom time format of AM/PM with no time.

    This seems to be a simple problem but the correct solution has eluded me.

    Any one have suggestions?

    • Use [$-F400]h:mm:ss AM/PM

      But you have to be on a 12 hr clock not 24 hr

  13. hi, i need help in excel time sheet.
    Example data available
    A = 15:55
    B = 20:25
    C = 30:30
    Total = 66:50 (by time formula)

    I need result in number
    A = 15.55
    B = 20.25
    C = 30.30
    Total = 66.10 (by number formula)

    Please help us

  14. Hi How to calculate 108 hrs post from 11/07/2017, 6:00, kindly share the formula.

    Regards,
    Sowmyashree

    • Hi Sowmya,

      Thank you for your question.

      Please first select the cell with your data, click Format Cells -> More Number Formats -> Date and set format 7-11-17 6:00 AM. Then try the following formula:
      =A1+1/24*108

      Hope it helps.

  15. I have added daily overtime for the month in format hh:mm. Now I want to display separately Hours and Minutes.

    I can extract minutes from formula =Minutes(cell) but for hours greater than 24, it gives me wrong answer. for example, if the hours are 34, formula will minus 24 and give answer 10.

    Kindly advice.

  16. I hope someone can help. I have extracted time from an application. When I put it in excel it reads it as a text, that makes sense. Now, I am trying to convert back to time...it reads 126 as 1:26 - the time is actually 00:01:26...I have tried every trick I can think of. Can anybody help me figure this out?

  17. How i can get a current time with the seconds in the excel and also need to get current time in the below cells.

  18. Hi what excel formula would I need to enter to work out the How much overtime has beern worked between 09:00-12:00? From the example data

    A. B
    Start. End
    10:00 18:00
    18:00. 22:00

  19. I have two cells each with the MS Date and Time Picker Control 6.0 (SP4) in a different cell I want to return the difference in # of business days between the two selected dates.

    thanks in advance!

  20. Hi

    Can I extract hours from format HH:MM having more than 24 hours. For example, I summed-up overtime for month = 56:34, How can I extract "56" using hour function?

    Regards,
    Saad Kapadia

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)