Time formats in Excel: 12/24 hour, custom, default (2023)

Time format in Excel

If you were following usExcel Date Format Guide, you know that Microsoft Excel stores dates as consecutive numbers starting with January 1, 1900 as the number 1. Since the hour is a fraction of a day, the hour is stored as a fraction.

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 p.m. is 0.5

When a date and time is entered into a cell, the value is stored as a decimal number consisting of an integer representing the date and a fraction representing the time. For example,June 1, 2025 at 9:30 amFiled as 45809.39583.

How to get a decimal number representing time in Excel

To get a decimal number representing a specific time, follow these steps:

1. Select the cell containing the time.
2. accordingCtrl + 1Opencell formattingdialog box.
3. existsnumbercard, select itGenerallylobe, belowcategory, you will see a decimal pointsampleBox.

Now you can note the number and clickCancelClose the window. Alternatively, you can clickWelland replace the time with the appropriate decimal number in the cell.

To keep the original time and its decimal representation, enter a simple formula like =B3 (where B3 is the time value) in any blank cell and replaceGenerallyNumeric format for this cell.

This is the fastest way to convert time to decimal value in Excel. You can also use another formula forConvert 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 into a cell. For example, if you type20:30 Saturday, the8:30 afternoon, even20:30 Saturday, Excel will interpret this as a time and display20:30 Saturdaythe8:30 afternoon, depending on yoursdefault time format.

To change an existing time format or apply a specific time format, use thiscell formattingThe dialog box is described below.

1. Select the cells to format.
2. accordingCtrl + 1Opencell formattingdialogue.
3. existsnumbercard, select ityearfromcategorylist and select the format you wantsuggestiondescription.
4. Click OK to apply the selected formatting and close the dialog box.

Custom time formats in Excel

Although Microsoft Excel offers many predefined time formats, you may want to create your own that best suits a particular worksheet. This can also be done usingcell formattingdialogue:

1. Select the target cell.
2. accordingCtrl + 1Opencell formattingdialogue.
3. lobe, belowcategory, selectcustomizedme usuggestionBox.
4. reviewsamplevalue to verify that it is formatted as expected.
5. Press OK to save the changes.

For example, to format date values ​​such asJune 1, 2025 at 9:30 am, use this code: dd-mm-yyy h:mm MM/PM.

The custom time format you create will be in thesuggestionMention it the next time you need it.

advice.The easiest way to create a custom time format is to use an existing format as a starting point. To do this, click onyearfrom the insidelist of categories, then select one of the predefined formats belowsuggestion. then go tocustomizedand change the viewsuggestionBox.

Time codes in Excel format

When creating a custom time format in a worksheet, you can use the following code.

12 hour time format in Excel

To set the 12-hour time format in Excel, typecell formattingdialogue.

To change the time to 12-hour format, you can also usefunction textUse one of the codes above.

Assuming the initial time value is in A3, the formula is:

=TEKST(A3; "hh:mm:d AM/PM")

notes.The TIME function converts a time value totext string.If you plan to calculate the time later, usecell formattingdialogue.

24 hour time format in Excel

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

After determining the format code, apply the appropriate onecustom formatin the source cell or pass the format code to the TEXT function to return the formatted time in another cell. Remember that in the latter case the output will be a text string.

The initial time is in A3, the formula is as follows:

=TEKST(A3;"hh:mm:dd")

Excel time format for more than 24 hours

Adding time, the total can exceed 24 hours. To properly format times longer than 24 hours, enclose the time code in square brackets, such as [h].

Here are some examples of non-24-hour time formats:

For more information, see howShow, add, remove within 24 hours 60 minutes 60 seconds.

Excel date and time format

Create custom formats fordate and time, using different combinations of time anddate format code.

The following table contains some examples of Excel date and time formats:

Excel time format without date

To format a date value so that only the time in the cell is visible, use only time codes, not date codes. At this point you can use international standard notation, e.g.Minutes and secondsor your custom symbols. Here are some examples:

How to format negative time values

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

• show aempty cellFor negative time. Type a semicolon at the end of the time format, e.g.[Good;
• show aerror message.Type a semicolon at the end of the time format followed by the message in quotes, e.g.[h]:mm;"Negative time"

Generally, the question mark is used as a delimiter to separate positive and negative forms. For more information seeCustomize Excel number formatting.

If you want to display negative times asnegative value, such as -10:30, the easiest way is to switch to Excel's 1904 date system. To do this, click onFile > Options > Advanced, scroll down toWhen calculating this workbookunity and controlUse the 1904 date systemBox.

For more information seeHow to count and display negative numbers in Excel.

Excel's default time format

When setting the time formatcell formattingdialog, you may have noticed that one of the formats starts with an asterisk (*). This isdefault time formatin your Excel.

To quickly apply Excel's default time format to a selected cell or range of cells, go toDom> tabnumbergroup and selectyearfromnumber formatdrop down list.

Download the practice book

Excel time format - example(.xlsx file)