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

This tutorial covers the basics of Excel time formatting and more.

Microsoft Excel has some time functions and knowing them in detail can save you a lot of time. To take advantage of powerful time features, it helps to understand how Excel stores times. In this article, you'll find everything you need to know about formatting time in Excel:

## 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.

(Video) How to Convert 24 hrs to 12 hrs Time Formats in MS Excel (Easy)

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.

(Video) How to Change Clock to 24 Hour Format from 12 Hour in Windows 10? | Change Time and Date Format

## Time codes in Excel format

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

codedescribeappears as
Hhours without leading zeros0-23 (appearance, specialist).
Hmmhours with initial zero00-23
riceminutes without zeros0-59 (appearance, specialist).
mmminutes to zero00-59 (display, professional).
Butseconds without leading zeros0-59 (appearance, specialist).
SSseconds with a leading zero00-59 (display, professional).
morning afternoontime of day
(if omitted, 24-hour time format is used)
before noon or afternoon

## 12 hour time format in Excel

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

Formappears as
h:m:sun before noon/pm13:30:00
Height: mm morning/afternoon13:30 Saturday

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.

Formappears as
Minutes and seconds13:30:00 Saturday
Good13:30 Saturday

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.

(Video) How to Remove AM/PM from Time in Excel | Format Time as Duration - Elapsed Time Format

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:

Formappears asexplain
[Good41:3041 hours and 30 minutes
[h]:mm:dd41:30:1041 hours 30 minutes 10 seconds
[h] "sati", mm "minute", ss "second"40 hours 30 minutes 10 seconds
h:m:dd1 17:30:101 day 17 hours 30 minutes 10 seconds
d "天" h:mm:ss1 and 17:30:10
d "days", h "hours", m "minutes" and "s" seconds1 day 17 hours 30 minutes 10 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:

Formappears as
h-mm-dd h:m:d AM/PMJanuary 13, 25, 1:30 p.m
mmm dd, hh:mm am/pmJanuary 13, 1:30 p.m
dd, m/d/yy h:m:ssMon, 13 Jan 25, 13:30:00
mm, mmmm mm, gggg hh:mmMonday, January 13, 2025 at 1:30 p.m

## 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:

(Video) How to Customize the Date and Time Format on Windows 10

Formappears as
h:m:sun before noon/pm13:30:00
h:min:ss before noon/pm01:30:00 p.m
Minutes and seconds13:30:00 Saturday
hmmmm13-30-00
hmm.m.s.s13.30.00 Saturday

## 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.

### How to change the default time format

To change the default time format in Excel, you need to do the following:

1. OpenChecklistthen clickterrain.If your control panel is incategoryview and then clicktime and zone>Change the date, time or number format.
2. from the insideterraindialogue, settingsshort timeIA long timeformat you want.

Now that you're familiar with time formatting in Excel, you can more easily manipulate date and time values ​​in your worksheets. Thanks for reading and we hope to see you on our blog next week!

(Video) How set the time in Windows 10 to show a 24 hour clock

Excel time format - example(.xlsx file)

## Maybe you're right. . . I'm interested in

• How to enter the current date and time in Excel
• How to calculate time in Excel
• How to remove numbers, percentages, dates and times Excel
• How to split and manipulate #DIV/0 in Excel! error

## FAQs

### How do you custom format 24 hour time in Excel? ›

Right click the selected cells and then click Format Cells, or press Ctrl + 1. This will open the Format Cells dialog box. On the Number tab, under Category, select Custom, and type one of the following time formats in the Type box: Over 24 hours: [h]:mm:ss or [h]:mm.

How do I create a custom time format in Excel? ›

Create a custom date or time format

On the Home tab, click the Dialog Box Launcher next to Number. You can also press CTRL+1 to open the Format Cells dialog box. In the Category box, click Date or Time, and then choose the number format that is closest in style to the one you want to create.

What is the default format for time in Excel? ›

Excel default date time format yyyy-mm-dd hh:mm:ss versus yyyy-mm-dd hh:mm.

How do you format time to 24 hours? ›

24 Hour Clock Converter: How to Convert AM/PM to 24 Hour Time
12 hour clock24 hour clock
08:00 PM20:00
09:00 PM21:00
10:00 PM22:00
11:00 PM23:00
20 more rows
Jan 23, 2020

How do I format time only hours in Excel? ›

Time Duration in Excel
1. On the Home tab, in the Cells group, click Format, and then click Format Cells.
2. In the Format Cells dialog box, click the Number tab.
3. Under Category, click Custom.
4. In the Type box, type [h]:mm. ...
5. Click OK.
Mar 26, 2022

How do I create a dynamic time in Excel? ›

Press Ctrl+; (semi-colon), then press Space, and then press Ctrl+Shift+; (semi-colon). Sometimes you may want to insert a date or time whose value is updated automatically. You will use a formula – the “TODAY” and “NOW” functions – to return a dynamic date or time.

How do you change the default date format in Excel? ›

Here's how to do it:
1. Step 1: Open Excel Options. Click on the "File" tab in the top left corner of the Excel window. ...
2. Step 2: Select the "General" Tab. Once you are in the Excel Options menu, select the "General" tab on the left-hand side.
3. Step 3: Choose Your Default Date Format. ...
4. Step 4: Save Your Changes.
Apr 5, 2023

What is the default date time format? ›

The "O" or "o" standard format specifier corresponds to the "yyyy'-'MM'-'dd'T'HH':'mm':'ss'.'fffffffK" custom format string for DateTime values and to the "yyyy'-'MM'-'dd'T'HH':'mm':'ss'.'fffffffzzz" custom format string for DateTimeOffset values.

How do you work with time periods that exceed 24 hours in Excel? ›

The square brackets around the "h" instruct Excel to calculate times beyond 24-hour intervals, which allows you to enter times that exceed 24 hours. If you enter times that exceed 24 hours, Excel automatically applies a number format that includes the [h] code. There are limits that apply to times that use this format.

What is the default date format? ›

By default, the date format is MM/DD/YYYY HH24:MI:SS.US. uses the default date format when you perform the following actions: Convert a date to a string by connecting a date/time port to a string port. converts the date to a string in the date format defined in the data viewer configuration.

### How do I convert 12 hour format to 24 hour format? ›

For example if the time in 12 hour clock is 11:00 PM, then the time in 24 hour clock is 11:00 + 12:00 = 23:00 hours. Q.

How do I change my 12 hour format to 24 hour format? ›

There's no 24:00 in 24-hour time since it moves from 23:00 (11:00 PM) to 00:00 (12:00 AM). Eliminate “AM” for the hours between 1:00 and 11:59 AM. Changing the hours between midnight and noon from 12-hour time to 24-hour time is super simple. All you have to do is take away the “AM” signifier.

How to convert each 24 hour time clock to 12 hour time format? ›

To convert 24-hour time to 12-hour time, follow the instructions below:
1. From 00:00 to 00:59: Add 12 and AM.
2. From 01:00 to 11:59: Add AM.
3. From 12:00 to 12:59: Add PM.
4. From 13:00 to 23:59: Subtract 12 and add PM.

How do I use the hours function in Excel? ›

=HOUR(serial_number)

This function uses only one argument: Serial_number (required argument) – The time that contains the hour we want to find. It will return the hour of the time value. The hour given will be an integer between 0 to 23 where 0 represents 12:00 AM and 23 represents 11:00 PM.

What is the time formula in Excel? ›

Example
HourMinuteSecond
164810
=TIME(A2,B2,C2)Decimal part of a day, for the time specified in row 2 (12 hours, 0, minutes, 0 seconds)0.5
=TIME(A3,B3,C3)Decimal part of a day, for the time specified in row 3 (16 hours, 48 minutes, 10 seconds)0.7001157
1 more row

How do I get 12 hour time in Excel without AM PM? ›

In the Home Tab, Numbers group, press the More button at the lower right. This time, you want the number format to be simply h:mm. So no AM/PM necessary.

How do I change the default date format in Excel Online? ›

To do this follow the steps below.
1. Click the File tab and then click the 3 dots for access the More File Options.
2. Select Options.
3. Now click Regional Format Settings.
4. Select your preferred regional format from the drop-down list and then click Change.
Feb 9, 2022

Why can't I change the date format in Excel? ›

This problem is caused by the dates and times being stored as text values. The solution is to use Text to Columns to convert the dates and times to numeric values. You will then be able to apply date and time formats in the normal way.

What is the default format of cells in Excel? ›

By default, all worksheet cells are formatted with the General number format. With the General format, anything you type into the cell is usually left as-is. For example, if you type 36526 into a cell and then press ENTER, the cell contents are displayed as 36526.

How do I convert time to military in Excel? ›

Get Military Time using Custom Formatting
1. Once you select the cell, use the shortcut key Control + 1 to open the format options.
2. Click on Custom to go to the custom formatting option.
3. From here, click on the input box and format that you already have there.
4. In the end, enter “HHMM” as a format and click OK.

### How do I change time format to duration in Excel? ›

Time Duration in Excel
1. On the Home tab, in the Cells group, click Format, and then click Format Cells.
2. In the Format Cells dialog box, click the Number tab.
3. Under Category, click Custom.
4. In the Type box, type [h]:mm. ...
5. Click OK.
Mar 26, 2022

How to convert 12 hrs to 24 hrs? ›

For example if the time in 12 hour clock is 11:00 PM, then the time in 24 hour clock is 11:00 + 12:00 = 23:00 hours. Q.

What is the formula for time conversion in Excel? ›

If you have a list of numbers such as 1, 2, 3.5 and you want to convert them to a time format, for example 1:00:00, 2:00:00 or 3:30 AM, perform the following steps. Divide the numbers by 24 (there are 24 hours in a day). The formula can be as simple as =A2/24.

Is there a formula for military time? ›

Standard times 1:00 AM to 9:59 AM — add a zero before the first digit, remove the colon, and remove the AM/a.m. Standard times 10:00 AM to 11:59 AM — remove the colon and the AM/a.m. Standard times 12:00 PM to 12:59 PM — remove the colon and the PM/p.m.

How do I format 12 hour time in Excel without AM PM? ›

In the Home Tab, Numbers group, press the More button at the lower right. This time, you want the number format to be simply h:mm. So no AM/PM necessary.

How do I format hours and minutes in Excel? ›

Customize the Time Format
1. Right-click the cell that you want to reformat to time in your Excel worksheet and then click "Format Cells…" to open the Format Cells dialog.
2. Click on "Time" in the Category box of the Number tab.
3. Select the time format you want to use in the "Type" box.

What is a time format? ›

Time is based on a 12 hour system. AM or PM is given. Example: 02:18:23 PM. 3. HH:MM.

How do I change the time format on a spreadsheet? ›

2. Highlight the data you want to format.
3. Click Format. Number.
4. Click Custom date and time.
5. Search in the menu text box to select a format. ...
6. Click Apply.

Why can't I change the time format in Excel? ›

This problem is caused by the dates and times being stored as text values. The solution is to use Text to Columns to convert the dates and times to numeric values. You will then be able to apply date and time formats in the normal way.

## Videos

1. Change Clock To 12 or 24 Hour Format In Windows 11
(The Geek Page)
2. Excel Essentials -- Level UP! -- Conditional Formatting for Due Dates and Expiration Dates
(Rob Hambleton)
3. Easy MS Excel - How to convert different date formats in to same format
4. How to Change the Date and Time Format in Outlook?
(Mail Smartly)
5. How to Change Date Format in Excel (the Simplest Way)
6. 25 Date and Time Functions in Excel You Need to Know
(How To Excel)

## References

Top Articles
Latest Posts
Article information

Author: Mr. See Jast

Last Updated: 08/09/2023

Views: 5335

Rating: 4.4 / 5 (55 voted)

Author information

Name: Mr. See Jast

Birthday: 1999-07-30

Address: 8409 Megan Mountain, New Mathew, MT 44997-8193

Phone: +5023589614038

Job: Chief Executive

Hobby: Leather crafting, Flag Football, Candle making, Flying, Poi, Gunsmithing, Swimming

Introduction: My name is Mr. See Jast, I am a open, jolly, gorgeous, courageous, inexpensive, friendly, homely person who loves writing and wants to share my knowledge and understanding with you.