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
I'm trying to convert a metric figure, calculated from 'Distance in km' divided by 'Speed in kph', and display it as a duration in hr:min format, instead of metric format. i.e. 126km/75kph = 1.68hr (or 16hr:19min when formatted).
I would like the result to read as a duration of 1hr:41min, (rounded up from 1hr:40.8min; calculated in two stages instead of one).
I tried using the Format Cell variations on offer in Excel but they return these figures: 16:19 or 40:19, which is not the return I'm looking for. I'm looking for a duration not a time. Can you help me please.
Hello!
Please check out this guide to learn how to convert numbers to time format in Excel.
=126/75/24
And set the time format in this cell.
I have little problem
when I try to make a cell take a now time and date but fix it , so it can't change again
when I make the formula, it retrieves fixed date 1\0\1990 12:00 AM
its different from the true time
Hello!
If you want to insert the current date as unchangeable, I recommend reading this comment.
Hi,
The timestamps are:
2:34:14 AM
2:44:10 AM
2:44:12 AM
To get the difference, I have applied formula as +B2-A2. Also, applied MAX(A2:B2)-MIN(A2:B2).
I get the differences as 0:09:56, 0:00:02.
When I use conditional formatting to find the greater value, it highlights 0:00:02. When I convert to numbers, the first value shows 0.01 and the second value 1.00 which is greater. How can I get the true max value and fix this issue? The issue occurs only in some cells.
Hello!
I cannot guess how you determine the maximum time. I recommend using the built-in "Top 10 Items" conditional formatting rule (set 1 value instead of 10).
Time in Excel is written as a number. What numbers do you convert it to and with what formulas?
I have converted the below timestamps from:
023414.000000 to 2:34:14 AM
21-09-09 02:44:10 to 2:44:10 AM
21-09-08 22:44:12 (4 hours add) to 2:44:12AM.
To convert the time stamps I have applied TIME(LEFT(A2,2),MID(A2,3,2),MID(A2,5,2).
Is the above formula applicable or I need to use any other formula to convert?
Then I have applied the subtract formulas.
Hello!
21-09-08 22:44:12 + 4 hours = 22-09-08 02:44:12
Difference between 22-09-08 02:44:12 and 21-09-09 02:44:10 02:44:10 is not 0:00:02 but 1day 0:00:02.
You have set the time format in the cell, so you don't see the days.
That’s a an important learning. Can you suggest any formula that extracts the exact time only without date value?
Hi!
It is necessary to extract the fractional part from the number.
=A2-INT(A2)
This should solve your task.
Thank you. I will try this.
I would like to use the Now() Function in Excel, creating a 1 count value every 30,60 ...minutes
Example: Your Machine Produces 1 Teddy bears every 30 minutes
Cell 1A. Cell 1B
Now() 12:00pm. 1
Now() 12:30pm. 1
Total. 2 Teddy's
Hello!
You need to add 30 minutes to each new cell. To know how to add hours and minutes, I recommend this article.
Hello,
I am trying to convert 12:00:00 AM as 00:00:00 instead it is giving the time as 12:00:00. How can I get build a formula which includes that can identify 00 and 12with change of AM and PM?
Hi!
I used hh:mm:ss time format and could not repeat your problem. You may not have provided all the details.
Hi,
Text 46:45, i used =TIME(LEFT(X2,2),RIGHT(X2,(LEN(X2)-FIND(":",X2))),0)
And out put is 22:45, but need output 46:45
Please suggest next
Hello how do I change the time format in excel from 9:00am to 9:00 AM so I can sort the times in column for earliest to latest time? I keep getting an error indicating the time is listed with text. I know part of the problem is the am attached to close to the time. Is there a way to create a formula to create a space so all the time stamps in the column will default to reflect time as 9: 00 AM or PM so I can sort the column.
Thank you in advance for reviewing and answering my question
How record the constantly changing value in a cell according to the time interval. For Example: If the value in A1 is changing like1,2,3..... How can i record this value according to every 15 minutes interval
I want a formula that will get minus 8 hours to my current time
Example a1 my time is 00:40, i want in b1 it will show what it will be if i subtract 8 hours from a1
Hi!
I recommend reading this guide: How to calculate time in Excel - time difference, adding / subtracting times.
Pay attention to paragraph — How to add or subtract hours to time in Excel.
=IF(A1-TIME(8,0,0)<0,A1-TIME(8,0,0)+1,A1-TIME(8,0,0))
Hello, I have C1 as minutes and E1 as a total count with F1 as =SUM(C1/E1) to give me count per minute. That count per minute is in decimal form. How can i have F1 as a decimal and have it equal G1 in time. For Example
C1-1
E1-2
F1-.5
G1 .30
Hello!
If I understand your task correctly, to convert decimal minutes to seconds, multiply by 60. If you want to write seconds as a decimal fraction, divide by 100.
0.5*60=30 30/100=0.30
Thanks Alexander. The issue im running into is if you flip it to
C1-3
E1-2
then F1(C1/E1)= 1.5 in excel
but i need a formula in a seperate cell for that 1.5(decimal) to show 1.3(time)
Hello!
You can use this formula:
=TIME(0,C1,0)/E1
Use custom time format "m.ss"
8:23:27 PM
9:00:29 PM
7:18:52 PM
11:02:54 PM
7:08:30 PM
5:22:12 PM
8:00:54 PM
9:03:58 PM
7:27:46 PM
7:54:33 PM
4:00:27 PM
3:30:37 PM
9:33:02 PM
4:20:26 PM
how to change the time min only
Hello,
I would like excel to calculate non numeric cells in increments of 10 and when it reaches 60 return 1 hour.
I am creating a break list that goes in 10 minute increments with 20 or 30 minute breaks. I need excel to count each cell as 10 minutes and when it reaches 60 minutes count 1 hour, if there are breaks longer than 30 minutes, I want them deducted from the total working time. I do not know how to convert the result to hours. =((COUNTA(D2:CI2)*0.1)-(COUNTIF(D2:CI2,"R")*0.1)) - this is the formula I have used, I need to know how to convert the result of this formula to time in accumulating hours, or if there's a better formula I ca use for my calculation.
Hello!
If you want to count the number of hours as a decimal number try this formula:
=((COUNTA(D2:CI2)*10/60)-(COUNTIF(D2:CI2,"R")*10/60))
I hope it’ll be helpful.
Good day Alexander,
Thanks a million, the formula worked beautifully, doing exactly what I need.
HI Team,
I am in need of Time calculation of my working hours on a specific task, My working hours is from MONDAY to FRIDAY ( 8 AM to 5 PM ), Eg: I got a task on Friday opened date of task 30/04/2021 17:18:00 and closed date of task is 03/05/2021 08:17:00 , As per human calculation as per my working hours it should be 17 minutes, But could any one guide me with Formula for this calculation
I need to take this Timestamp and turn it into the proper date and time, hour, min, sec.
1614828584677 = ?
I would like to convert start and stop times to total minutes, ex. 17:46 to 20:00 is 2:14, but I would to display as 134 minutes. Help is appreciated.
Hi,
To express the time in minutes, multiply by 1440.
=A1*1440
Hope this is what you need.
I have a cell in the time format hh:mm:ss (eg. 0:10:00). I want it to just show the minute i.e. 10 minutes for the example. To follow up if it is 0 minutes, can it display 0 instead of 00?
Hello!
In time format, either hours or seconds must be shown along with minutes.
You can use the "hh:mm" or "mm:ss" format.
The "mm" format will show not minutes, but months.
Sample
London Dubai Bangkok Auckland
Mon 2 Feb 12:00 Mon 2 Feb 16:00 Mon 2 Feb 19:00 Tue 3 Feb 01:00
Scenario: I have an Excel file and it contains a future event date / time in London.
I would like that date / time to change dynamically, returning the correct date / time in wherever I open the file.
Can I use a formula which checks the time from the computer zone setting e.g. =NOW() and then add or subtract that local time from the given London time so that the future event time is correct for that specific location? Thanks.
Dennis
Hello!
For each time zone, you need to change your computer's system time to the desired number of hours.
Here is the article that may be helpful to you: How to add or subtract hours to time in Excel
hi, I need an Exel formula to give the time "now" in A4 when an Input is made in A1
eg:
A1 A2 A3 A4
trailer No Dock Dist Time
Hi,
This comment answers your question.
How to get a time using Number 0 and 1, when we type 1 in C column then D Column should reflect the system time and in case if it is 0 in C Column then D Column should reflect blank.
For example- If Column C1 is '0' then D1 should shows Blank similarly if Column C1 is '1' then D1 should show the system time.
Hi,
If I got you right, the formula below will help you with your task:
=IF(C1=0,"",NOW())
I hope this will help
it's ok , but have 1 problem . i input A1 columm 0>1 then show current time A2 columm and another day input B1 columm 0>1 then show A2 and B2 sametime.
Hi,
I didn't quite understand your problem, but maybe you will find this answer useful in our blog.
How do I compute running hours, supposed from 06:00 AM to 09:00 AM? the result should show 3 Hours but it always shows error when I followed the instructions here. Thanks!
Hello!
For me to be able to help you better, please describe your task in more detail. Please specify what formula you used and what problem or error occurred.