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

255 comments

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

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

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

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

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

  6. 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?

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

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

  9. 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!

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

  11. Hi,

    i am having a problem with the date.
    Issue : i have a excel sheet i have exported it from a software. When is share this file from personal machine to my work machine the date changes from \ to - but when i send it to my client it works fine from same machine.
    i have changes the format locale to English(United States), now i am able to get the date as 14\02\2017 but the seconds in time stamp is missing in few cells
    for E.g date(in my machine)-14\02\2017 02:10:55
    in work machine -14\02\2017 02:10
    But in few cells the date is displayed as expected but not in few.
    Moreover, when i select the cell where ss is missing i am able to see date in formula bar as 14\02\2017 02:10:55 PM
    Will you be able to help me ? it's bit urgent

  12. Hello!

    I have formatted Cell to CUSTOM dd"d"hh:mm and getting results 04d04:04
    for 04 Days 04 HRS 04 MIN. 3.16997 Days which is exactly what i want.

    But considering the same formatting i am getting result 01d04:04
    for 00 Days 04 HRS 04 MIN instead of 00d04:04.

    Hope you understand my query, and please help me to get result 00d04:04 as per above explanation.

    Aizaz

  13. Hi.... please help me in using time (0,0,0) for more than 24 hours format. I mean the railway time.urgent

  14. Hi

    I looking for the calculation of date and time like

    01/03/16 4.15 PM to 01/03/17 5.15 PM

    Need Answer like this ways....

    Total Years Total Months Total Days Total Hours Total Min Total Sec

  15. Hello,
    I have taken static time format and found the time difference by =INT(F25-H25)&" Days "&HOUR(MOD(F25-H25,1))&" Hour "&MINUTE(MOD(F25-H25,1))&" Minutes" this formula.
    Now, I want to include only the working minutes and exclude the non working days( saturday,sunday and festivals etc) and non working hours (after 6.30pm to 9.30am). It will be very helpful to me if you could tell me the solution asap

  16. Hello, here is my example.
    A plane departs at 10/01/2017 01:00:00 and passengers come to gate 02:00hrs prior, which is the previous day 23:00:00.
    How to subtract those 2 hrs from cell containing 10/01/2017 01:00:00? Many thanks!

  17. I would like to track my improvement over time as I run. I would like to design an Excel spreadsheet that I can glance quickly at and see the number of days I have achieved, or nearly achieved, this goal by the color of the cell. Is it possible to design a spreadsheet which works like this?
    All of this would apply to each cell in a column thirty-one rows high:
    If time Entered is greater than or equal to 14:52:00, then shade cell red
    If time entered is between 13:52:00 and 14:51:00, then shade cell yellow
    If time entered is between 12:00:00 and 13:51:00, then shade cell green

    One of the major problems I have encountered doing this is that excel converts my minute:second input into time (for example, when I type 15:30, it corrects it to 3:30 PM). I really appreciate your help with this. I am fairly new to Excel and have learned a lot using the information on your site. John

  18. Hi,
    Pls suggest me how to count the current date, the date is with timestamp as well

  19. Hi,
    I am using the form data,and there are n number of rows, since i want to count the current date(today) responses. i have used the formula as below but it is not reflecting the result.
    =COUNTIFS('CCR-2017'!$C$3:$C,"="&TODAY(),'CCR-2017'!$E$3:$E,"GUNA")

    if the C:C column is with only date (Without time stampl) it will reflect the result. due the time stamp my formula not calculating the current date.

    Can you pls help me on this.

    Regards,
    Mahesh

  20. I'm trying to calculate time difference (start time - end time = total time) but also enter start/end times without having to type the colon. If I format the time so I don't type the colon my formulas won't work.

    I'm using this formula: =TEXT(+H2-E2,"00\:00")+(E2>H2) which works .... but ONLY if the seconds in my end time are larger than start. Otherwise, it calculates based on 100 versus 60 (for time).

    The time format I'm using so I don't have to type the colon is: 00\:00

    Is there a way to quickly enter times (w/o the colon) but have it calculate based on 60 minute increment.

  21. how could i use =NOW() , which shows time but it will not change/update automatically. when i'll use this then it will only shows insert time only. please help for it.

  22. How can i check if there is an update in excel file

  23. Hi Svetlana,

    Is there a way to format the cell to show GMT-0500 at the end of a date/time cell to indicate the time zone?

    Example: 2016-10-17 03:10:42 -0500 or 2016-10-17 03:10:42 GMT-0500

    Thanks,
    G

  24. i have to print a value in another column.The condition is if the time exceeds 12:00:00,add 1. Time is mentioned in H column and the data in which addition is to be done is in column G. The value to be returned is in column J.
    I am using formula "=if(G2>"12:00:00",sum(G2,1),G2)".This formula doesn't implement in some cases. How to do it?
    PS: Time is in 23:59:59 format.

  25. Hi - how do I set a conditional formatting that accounts for a potential date change due to the 24hr time? For example, if a therapist visits a patient on X date but at the end of the day 4pm, the date will change to the following day. Can you assist with the formula for this?

    I currently am using this formula: =IF($J$25="","",($J$25+139))+1 for date
    &
    this formula for time: =IF($W$25="","",($W$25-TIME(0,15,0)))

    Thanks!

  26. Hello-

    How can i have the time enter automatically when someone selects or clicks a name from a drop down list in a different column. Is that even possible?As opposed to entering time manually.For exmample lets say column B has a drop down list of people and i want the time to auto populate in column q with the start time when someone clicks a name in that cell(B2). Each person would have to manually or short cut the end time. Time can vary between people.

    B3 Q3
    Moe 10:30 PM
    Larry 10:45 PM
    Curly 10:57 PM

  27. I want set the all the values till end of the record to the previous day 11:59 pm how to I do that

  28. I have some time based trading data in seconds, but some of the seconds are excluded from the table since no trading happened then. How can I insert the missing seconds?

  29. In time 21.50 ( night 11.50) and closing time 7.00 am but time not calculate can u help me.

    no need negative value

  30. In time 21.50 ( night 11.50) and closing time 7.00 am but time not calculate can u help me.

  31. Hi,

    Just wondering how to get the correct duration (time elapsed), for example, between 23:00 and 00:05. I used simple subtraction but it returns negative result.

    thank you

    • Hi Steven,

      I have just tried this:

      A1 is 23:00
      B1 is 00:05

      The formula =A1-B1 returns 22:55

      Do you get a different result in your Excel?

  32. Hello Sir,
    I need to represent data to show the jobs completion time on a given week.
    On 30th july, the job completed the next day ie 31st july at 12:43 am.
    since my x-asis has time frame of 0- 24 hours, the graph is not picking the next day completion time.

    Pl can you help me out to get the graph even though the job completed the next day
    thx

  33. How to convert DD:h:mm in to H:mm formate For Ex. 02(Day):01(Hours):10(Min.) to 49(Hours):10(Min) in excel.

  34. KAD6336 1 01/06/2024 13:49
    RVS2196 1 01/06/2025 20:39
    LAG3131 1 01/07/2015 21:59
    ADA2259 1 01/07/2016 03:28
    LAG0060 1 01/07/2016 05:15
    ADA2312 1 01/07/2016 09:06
    UW3274 1 01/07/2016 09:24
    LAG0127 1 01/07/2016 11:10.
    Second post
    KAD6336 1 06/24 13:49
    RVS2196 1 06/25 20:39
    LAG3131 1 07/15 21:59
    ADA2259 1 07/16 03:28
    LAG0060 1 07/16 05:15
    ADA2312 1 07/16 09:06
    UW3274 1 07/16 09:24
    LAG0127 1 07/16 11:10
    LAG3216 1 07/16 13:44
    LAG3114 2 07/16 13:18

    Second post is what i got when i copied from whatsapp and pasted on world while the first is what i got when i copied and pasted on excel. please how can i get the time format to appear with the same format when i copy from whatsapp. Note:this occurs when whatsapp message is sent to me from another organisation. i dont have such issue when it is sent from my organisation. how can i get the format of the first post as it is in the original message. thanks

  35. I want to find hours left on a project ,so I have hours worked and hours billed =hours left ,how do i create a formular

  36. I am trying to calculating elapsed time in hours but I need to account for end times that are blank and to count those times as current time or newtwork hours.

  37. I have cells that time within for a schedule.
    ex: 0800-0830
    next cell underneath 0830-0900.

    I want t update the time in the 1st cell, is there a way for the time in the cell underneath to automatically update or to I have to go into each cell to update the times? thank you

  38. Hi,

    how to use Sum if for time units sum ??

    i have the list of time units for a month for more staffs ! how to get sum of Time units for staffs ! (Using Sum if)

  39. Hi,

    How do i convert 53212 mins into hh:mm:ss format i used the following to get hh:mm but im not getting SS please guide me in this behalf

    Tpe1 =TEXT(FLOOR(A1/60,1),"00")&":"&TEXT(MOD(A1,60),"00")

    Type2 =INT(A1/60)&":"&TEXT(MOD(A1,60),"00")

  40. Hi,
    I can't get this formula to work: =TEXT(TIME(HOUR(A1);MINUTE(A1);0);"hh:mm")
    Regardless of how I write the formula or format the cell, the result shows 'hh:01' and not the time given in the source cell, which itself contains a formula. The source cell lists both date and time, and I'm trying to extract the time. The file is an .xls-file, not sure whether that has an impact. I've run out of ideas of how to solve it, does anyone have a good solution?
    Kind regards

    • hi,
      please tell me
      16/11/2016 10:03:40 AM
      -16/11/2016 11:08:08 AM
      show me hours ..

      • Just type NOW()-INT(NOW())in Cell A1
        Then extract hours, mintues & seconds details from Cell A1
        A2=HOUR(A1)
        A3=MINUTE(A1)
        A4=SECOND(A1)
        I hope, it would serve as a solution to your issue & I'm not able to attach screenshot of the whole demonstration

  41. Whilst certainly not commonly used... I'm trying to keep my spreadsheets presentable and not confusing.

    Cell Formats for Time:

    Within the accounting function/format; if the cell = 0.00, -
    How do I get excel to do the same with time; if the cell = ([h]:mm) 0:00, - or "blank"

    You help is appreciated

    • Hi All,

      If you've seen this and started to play with it, thanks for your attempt... I have found my solution by playing:

      [h]:mm;;_-* "-"??_-;_-@_-

      Kindest Regards

  42. My time format is set to HH:MM:SS in excel but, it shows as H:MM:SS in spreadsheet, ie: 1:15.00; I want it as 01:15:00. Please help.

  43. Hi,
    I want to calculate over time of employees. Would you like to help me in this regards as 10:30PM-10:00AM= ?
    Regards

    • EXAMPLE= STARTING TIME(A), FINISHED TIME(B), TOTAL HOURS(C)
      FORMULA IS
      C=SUM(12+B)-A

    • A B C D E
      Working Starting Time Working Ending Time Actual Work hours Worked hours Over time
      10:30 AM 10:00 PM 9:00:00 11:30:00 2:30:00
      =C1-B1 =E1-D1

      All Cell Format should be : Format cells (Cntrl + Shift + F) > Number Tab > Time > Selecet "HH:MM:SS" I can be 7th one in list

  44. type '03/01/2015 on cell A1(or on any cell) and '11:34:01 A2(or on any cell).
    Then on any cell type =concatenate(a1," ",a2)
    typing apostrophe (') sign before any numerical value or any special character displays it as it is.

  45. hello,

    could you please help me ?
    I have cell contain date like (03/01/2015)
    and I have cell contain time (11:34:01)

    how can I put them in one cell to become (03/01/2015 11:34:01)

    could you please give me the format

    thanks
    ahmed
    from Iraq/ basra
    petroleum engineer

    • Hello Ahmed,

      You may use CONCATENATE functions to combine those two cells.

      hope this will help

    • CTRL+; for date
      CTRL+SHIFT+; FOR TIME

      • but this shortcut wont show seconds. How can i use this shortcut and it will include seconds as well...thanks

    • just use =NOW() and format.

    • =TEXT(A1,"dd/mm/yyyy")&" "&TEXT(A2,"HH:MM:SS")

      A1 = Date
      A2 = Time

  46. Hi,

    My query is,I will have to maintain In time and out time register for our staff to monitor overtime of work for which "I PREPARE FOUR COLUMNS, NAME, FATHER NAME DESIGNATION, GENDER IN TIME AND OUT TIME COLUMN, When I enter in time value, I need the out time value should be fill by counting 9 Hrs. from the time of In time Entry.

    Pleas advice me Sir.

    Regards,

    JRK Prasad

  47. Hi Svetlana, is there any wy I could insert the time greater than 24h (80 hours for example) using TIME(hh;mm;ss) function? Or is there any solution to compare the time... Ig the time is greather or equal to 80h?

  48. I want the negetive time to show as ZERO when subtracting the time..
    23:40 - 23:30 = ##### instead of that i want it to be 00:00

    • Hi Ali,

      You can use a formula similar to this:
      =IF(B2-A2<=0, "00:00", B2-A2)

      • Hi madam,

      • HI Madam
        Please help me actually i want to use a formula for my office which is very important for me. I want to calculate time like 02.30 to 3.10= 00.40, +13.30 to 14.00= =00.30 thier sum as 01.10 hrs in ecxel but i m unable to do the same.
        plz provide ur mail so i can send u excel sheet.
        Plz help
        regards
        Anil chopra +91-9992318962

  49. 48 hours example

    • Hi Anant,

      Sorry, I am not sure I can follow you. Could you elaborate a bit?

  50. please time 48 hours out put

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