by 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.
How to get a decimal number representing time in Excel
To get a decimal number representing a certain time, carry out these steps:
- Select a cell containing the time.
- Press Ctrl + 1 to open the Format Cells dialog box.
- 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.
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.
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.
- Select the cell(s) that you want to format.
- Press Ctrl + 1 to open the Format Cells dialog.
- On the Number tab, select Time from the Category list, and then choose the desired format from the Type list.
- Click OK to apply the selected format and close the dialog box.
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:
- Select the target cells.
- Press Ctrl + 1 to open the Format Cells dialog.
- Under Category, select Custom and type the desired format code in the Type box.
- Review the Sample value to check if it's formatted as expected.
- 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.
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")
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")
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.
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.
To change the default time format in Excel, this is what you need to do:
How to change the default time format
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)
269 comments
hi guys,
i need to know in my scenario..... how to show the result when its get mature
like... i have some checks due in next month which i want that when the date come, it automatically appear in my another maintained sheet.although, the date and all details with despcrition explained in first sheet but in other sheet i need their appearance
so i need to know how these details appear at the time of maturity
appreciate your response back
Hi team, I want to enter exact time, using Ctrl+Shift+; but it omits seconds, only shows hours and minutes (in hh:mm:ss format, i.e., for example: 11:34:00 against the timing of 11:34:45) please help.
Hi i would like to extract a data of the last 24 hours in my data base. which means i have to use intervals. i would like to use Now function to return me the last 24 hours data from now.
these values will be updating hourly as the data base keeps changing.
Ex. when i want to extract data for the last 30 days i use today function for the time interval.
Start: today-32
end: today
this gives me 32 data and will always change the last day as time goes.
i would like to use similar method for hourly data extraction using NOW Function.
Start:
End: NOW
HOW CAN I GET THE STARTING TIME USING NOW'S FUNCTION AS I EXPLAINED WITH TODAY'S FUNCTION
Have a column to check if timesheet add-up correct. Am getting a ##### error sometimes and othertimes get what 0:00 result when using formula: =IF(G21="","",G21-SUM(I21:P21))
G21 is the sum of start, end, & breaks times. Columns I-P are the division of total work hours (G21). The problematic QC formula is the check if G21 and the sum of projects are equal & equal 0:00 hours. Note: All cells are in custom format: [h]:mm;@
EXAMPLE:
cell G21 formula: =IF(D20="","",($E20-$D20)-F20)
cell G21 result: 8:30
cells I21-P21 values: 0:30/1:00/1:30/0:30/1:00/1:30/2:00/0:30
cell I21-P21 result: 8:30
cell Q21 checking formula: =IF(G21="","",G21-SUM(I21:P21))
Sometimes given Q21 result of 0:00 and other times #######
I need to split date and time in two different cells from the below format
2019-06-21 18.36.30
Please can you tell me the easiest way to insert a static timestamp including seconds in a cell. If I use 'Ctrl + Shift + ;', I get say 23:35, but I need the seconds to appear too, e.g. 23:35:33. Please can you help? Thank you.
I am doing a Work Project where we need to calculate minutes used for activity, we use "=(P9-O9)*1440" but when going 23:00-00:10 instead of 70 it gives us -1370 Any tips?
Incase anyone comes across this same problem, you just have to add the Date before the time to solve it.
Hi!!
Im trying to use the =today()+1 formula, i need it to updated the date when i open the file but i need it to stay the same date (not update) at midnight
I want to determine if the current time is between two preset times. Let's say I want to know if the current time is between 10:00 (in A1) and 11:00 (in A2). If it is currently 10:30 and I enter "=now()>A1", I get a return of "TRUE". But if I then enter "=now()<A2", I get "FALSE". Why is that?
How do I add to this formula to start calculating time-sheets Where the first cell,first column is A1 (in time),second column is B1(out time) third column is C1(working hours), enter this in the first cell, fourth column:
=IF(C1>9,9,C1)
Nine is should be time format
I need fourth column (normal working hours it should blow the nine) and fifth column(it is OT hours C1-D1)
Noted all are Time format please help me....
Hi, I am trying to calculate how much time it took someone to close a ticket. The report that I get out of Service pro shows the dates like this:
Ticket # Time Logged Time Closed
12/28/2018 1:50 PM 1/7/2019 8:23 AM
I need to calculate the difference between the 2 time periods so that I can then average this amongst all of the tickets that were closed. Can anyone help me?
Let's say 12/28/2018 is in A1 and 1/7/2019 is in A2 and the number of tickets (let's it is 400) is in A3. =((A2-A1)*24+INT((((A2-A1)*24)-INT((A2-A1)*24))*60))/A3 will give you 0.668875, which is the number of minutes per ticket. Multiplying that number by 60 gives you 40.1325 seconds per ticket, on average.
I'm trying to convert the cells with a text format 5 days, 6 hours, 0 minutes and 0 seconds to
custom format like d hh:mm:ss in order to complete an in cell calculation. How can this be done ?
can anyone say me how i can convert 0:00 to 00:00 until to 9.
Hi
can you help me in speed data entry getting info by phone
0730
0835
0955
1045
1515
if my data entry like above i need this to convert to time or else need to put ":" in mid (I used left and right formula but i want the same cell value directly change to time format)
is there any formatting or other option to support my requirement please.
Thanks in advance
hi i have 79 hrs 30 mins which is written as 1/3/1900 7:00:00 AM on the dialog box ....now i want to convert it into 79.30...
how can i do this?? please help urgent
Please guide me to get the difference between two time stamps in the following format
20-09-2018 13:00:43 20-09-2018 15:30:31
Hi I am having a doubt in excel, I am having a table with 4 columns in which two fields are date field, If i enter a value in first column the current date and time should be entered in the second column(date column), is there any way to be done for this.'Thanks in Advance
Ram:
Where the first cell, first column is A1, enter this in the first cell, second column:
=IF(A1"",NOW(),"Empty")
It says: If A1 is not empty enter current date, otherwise enter "Empty".
Format the first cell, second column as DATE with the time included.
You can enter any text you deem appropriate in place of "Empty".
I am trying to edit a basic time card in Excel made by others. I put the time in: ie: Cell G:25, I want to write: 0800, and it fill in as 8:00, then Cell G:26 write 1300, and it fill 1:00, Cell G:27 write 1330, fill 1:30, Cell G:28 1630, fill 4:30. Then Cell G:29 totals how many hours I worked that day. 8.00. And finally in Cell M:29 a total of how many hours were worked that day.
I apologize if I don't make sense, but any help would be amazing!
Thank you.
7:46 7:46:00 AM
1:05:32 1:05:32 AM
CELL SHOWING 51:45:00 BUT INSIDE CELL 02-01-1900 3:45:00 AM
THIS IS EXPORT SITUATION FROM WEBSITE REPORT ACTUALLY THIS IS MINUTES AND SECONDS 51:45
Do I need formul to subtract time, like 88657:53 - 745563:32
Or do I have to change time in to numbers?