Comments on: Excel time format: 12/24 hour, custom, default

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. Continue reading →

Comments page 3. Total comments: 269

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

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

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

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

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

    1. Hi,
      If I got you right, the formula below will help you with your task:

      =IF(C1=0,"",NOW())

      I hope this will help

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

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

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

  6. Hi, i would like to calculate the 'Total Hours' from the 'Start Time" and 'End Time" and afterwards i would want to find out the man-hours from the 'No. of Workers' but in Hours format (2.5h and etc)
    Please help me with the format. Thanks!

    Example:

    No. of Workers Start Time End Time Task Duration Hours
    2 09:00 11:00 02:00 4

  7. I WANT THAT EMPLOYEE WILL ENTER VALUE IN CELL B2 THEN IN C2 THEN IN D2 THEN IN E2 CELL.
    THEN EMPLOEE WILL SAVE ENTRY. DATE AND TIME SHOULD AUTOMATICALLY APPEAR IN CELL A2.
    HOW CAN I DO IT?

  8. I want to calculate the duration over multiple days without having a date in the formula.

    for example

    C1/ D1/ E1
    Start time / End Time / Duration (what i want to work)

    22:00 / 02:30 / 04:30 (calculated total)

    I have also applied the 0/:00 Formula to the whole sheet and I tend to get totals of 0:00 now.

  9. I want to calculate the duration over multiple days without having a date in the formula.

    for example

    C1 D1 E1
    Start time End Time Duration

    22:00

    1. Hi,
      Your task is not completely clear to me. For me to be able to help you better, please describe your task in more detail. It’ll help me understand it better and find a solution for you.

  10. Hello,

    I need a formula that counts any time from a previous or current day as a full day.

    Ex.

    1/2/21 13:30 1/3/21 0700 2 full days
    1/2/21 13:30 1/2/21 1400 1 full day

    This is what I get:
    1/2/21 13:30 1/3/21 0700 0
    1/2/21 13:30 1/2/21 1400 0

    1. Hello!
      If I got you right, the formula below will help you with your task:

      =ROUNDUP(B1,0) - ROUNDDOWN(A1,0)

      I hope my advice will help you solve your task.

  11. how to record share price change per minute in Live trade market

  12. I was using a spread sheet that required a time on every line. it was programmed to use ` in place of :

    for instance, I could type 7`32 and the time 7:32 AM would appear.

    How do i recreate this?

    1. Helllo!
      You may have used Auto Correct Options (Excel options - Proofing). You can set up automatic replacement for ` to :
      I hope this will help.

  13. Hi

    Hoping someone can help.

    I have a time of day for example 09:00 but want it to be displayed as the hour it’s in I.e 09:00-10:00 is there some way to do this quickly please?

    1. Hello!
      Here is the formula that should work perfectly for you:

      =TEXT(A1,"hh:mm")&" - "&TEXT((A1+TIME(1,0,0)),"hh:mm")

      I hope it’ll be helpful.

  14. क्रमांक दिनांक नाम
    12755414 12/02/2020 8:37 सुशिल कुमार
    12993741 01/01/2021 17:36 मुन्नु लाल
    13010945 01/04/2021 10:04 बाला
    13081668 1/13/2021 12:39:49 PM मनोहर
    13082304 1/13/2021 1:40:49 PM नितीश
    13103407 1/16/2021 11:27:15 AM जितेश
    13108341 1/16/2021 7:52:29 PM अनिल
    13109480 1/17/2021 8:20:01 AM दीपक

    How to distinct date and time in excel from formula

    1. Use function "Text to Column" to separate these values in different columns

    2. Hi!
      For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find. Give an example of the expected result.
      It’ll help me understand it better and find a solution for you.

  15. how to record share price rate change per minute in Live trade market

  16. hello

    in excel i am using Now function to enter the current date and time.
    But i want to fix it.

    How i will do that?

    please help me.

  17. Please help

    two different date and time need difference of both date
    for eg

    12/12/2020 12:00 PM - 12/15/2020 17:00 PM = Difference time () in text format

  18. Hello,

    I'm running into a problem where using the Format properties to change a time value from 24hr to 12 hr is not working. I have to dbl-click in the cell and hit enter for the format change to occur.

    This is an example of the value in the cell: 17:00:00. It will not change to 5:00 pm unless I edit the cell and hit Enter.

    I've tried to use the Calculate Now, Calculate Sheet operations under the Formulas menu but nothing changes the time values.

    Help me, able-won-kenobi!

    Thank you!

    1. just go in data tab and refresh

    2. Hello!
      Unfortunately, I was unable to reproduce your problem on my own. When you change the format, the appearance of the cell changes immediately. How do you change the format? Could you please describe it in more detail? It’ll help me understand it better and find a solution for you.

      1. That's the problem, the appearance of the cell DOES NOT change immediately.

        The only way it changes is if I edit the cell and hit Enter. Then it changes to the desired format.

        I changed the format by highlighting the two columns of Time data, right click, select Format Cells ... and change to the desired format Time "1:30 PM".

        That's it. Very annoying.

        1. Hello!
          There is probably an error with the data formats in your file. Perhaps this method will help. Copy the date column to Notepad. Then copy the data from the Notepad and paste it in its original place. Apply the format you want.

  19. 2020-08-18T9:00:00+07:00
    How to create custom formula for this in excel?

  20. total duty Time - total Work Time
    216:00:00 212:16:00 -3:44 this answer ok
    but
    i use same formula
    216:00:00 182:19:00 -9:41 this answer wrong
    why
    this answer -33:41

  21. 216:00:00 212:16:00 -3:44 ok
    but
    same formula
    216:00:00 182:19:00 -9:41 wrong -33:41
    =IF(H7-G7>0, H7-G7, TEXT(ABS(H7-G7),"-h:mm"))

  22. Hello i wanna do a time format that counts down days till expiration and the way its set up is there is the date format in each row is it possible to make 1 date show and each column follow that date with multiple items and different expiration dates?

  23. Hello - Is there a source that shows how to create a formula that calculates the "duration" it takes someone to do something?
    Ex. All I have are "start time" in one column, and in the next "end time".
    Example, one column says the time that Jeff texted me = 07/10/2020 at 12:00 p.m.
    In the other column, it shows my response time to Jeff being the next day on 07/11/2020 at 06:00 p.m.
    How can I create a formula that would automatically give me "30 hours" - since the response time took thirty hours?

    1. Hello Isabella!
       Subtract the start time from the end time. Then apply a custom time format. To be able to show more than 24 hours in a cell, please use this time format: "37:30:55". You can find it in the list of Excel time formats.

  24. How can I do 23:50 - 00:00? For example if its 23:50 on a tuesday then 00:00 on a wednesday?

    1. Hello Rebecca!
      If I understand your task correctly, if the time difference is negative, add 1 day (24 hours)

      =IF(B3>B1,B1-B3+1,B1-B3)

      To the start time you need to add 1 day.
      I hope it’ll be helpful.

  25. I am entering 10.40 and the cell is changing it to 9.36 am automatically, please share why?

    1. Hello!
      Date is stored in Excel as a number. 10.40 is 1/10/1900 09:36:00. To get the time, you need to enter it correctly. Use a colon. (9:36:00).

  26. I have sum all my employee working house with =SUM(C2:C26), I got a total of 810:07 hours (format [h]:mm) When I tried to change the formatting to
    dd "day," h: "hours," mm "minutes. I got 2 days, 18 hours 07 minutes which is wrong! How to fix that?
    Thanks

    1. Hello Khalid!
      The date format in Excel cannot show the number of days greater than 31. Therefore, try to use such a custom time format

      m"month," d"day," hh"hours," mm"minutes"

  27. Hi how could i calculate the Night duty hrs which is inside the total hours & btn 22 to 06
    Id of Crew|Name |In |Out |D.Hrs|Night D.Hrs
    N10001 |AAA |05-06-2020 16:00|05-06-2020 23:00|07:00|01:00 (btn 22 to 06)
    N10002 |BBB |06-06-2020 23:00|07-06-2020 08:00|09:00|08:00 (btn 22 to 06)
    N10006 |XYX |08-06-2020 03:00|09-06-2020 23:30|20:30|03:00 (btn 22 to 06)
    N10010 |LMN |08-06-2020 21:00|08-06-2020 23:45|02:45|01:45 (btn 22 to 06)

  28. Hi how could i calculate the total time lapse i tried =sum(f2:f4) but its not working

  29. Can Excel actually figure out the time of 1:75 is actually 135 minutes?

  30. Can Excel actually convert the time from 1:45 to 1.75 through a formula?

  31. I want to add up hours for an employee and then multiply those hours by her rate to get total cost spent. How can I do this if the hour is in this format hh:mm:ss? The formula needs a whole number like 1.75 rather than 1:45.

    1. Hello!
      To convert time to number of hours, and minutes to decimal parts of an hour, use the formula

      =HOUR(D1)+MINUTE(D1)/60

      Hope this is what you need.

  32. I would like to record 24h00 in Excel 2013

    1. Hello Seleko!
      Please go to Format Cells, choose Number -> Custom Format and set
      [hh]:mm:ss;@

      I hope this will help, otherwise please do not hesitate to contact me anytime.

  33. Hello!
    Any one please help!
    i need to assign formula, if employee works after 9:00pm i need give one food allowance.

    1. Hello Bharath!
      Supposing your end time is in B2, the formula below will be the one for you:
      =IF(B2 < TIME(21,0,0), B2 - TIME(21,0,0) + 1,B2 - TIME(21,0,0))

  34. I have custom formatted cells with 00/:00 enable simple entry just directly key in number for instant 830 will display 8:30am,but when come to calculating the duration it display hh:mm, may I know how can I get it converted to single minute unit please.

  35. Hello,
    Is there any format that will make my time inputs in PM only. SO if I type in 5:00 it will automatically pick it up as PM and not AM.

  36. Hi, I want to record only time of a data entry is it possible to do so?

  37. I am trying to input hours in one column for example input and subtract
    in one column i have 24:13 minutes to be deducted from 113:45 minutes when i input 24:13 it changes to 0:13 also the current formula i use is =(C3-B4) where C3 is 113:45 and B4 is 24:13

    Thanks

  38. Hi,

    I've a doubt, i want to compare two columns in Time format and highlight the one that exceeded. I can only able to compare it, but I would like to know how to highlight it?

    For example:
    Shift time Login Time Status
    2:30 2:25 Met
    2:30 2:29 Met
    2:30 2:45 NotMet
    2:30 2:15 Met
    2:30 3:15 NotMet
    2:30 2:50 NotMet
    I can compare whether the condition is met or not, but how to highlight the cells in 2nd column automatically.

  39. Hi,
    How can I just display time instead of date & time in the cell. As I need 22:00 but the cell will show 14/05/1903 10:00:00pm.

  40. i am trying to create a timesheet that will limit the amount of "time worked" in a cell to 12 hours, because everything over 12 hours is considered overtime i was fairly certain my formula would be =IF ((C1-B1, "h:mm:ss") *24) > 12 , 12 ,(C382-B382, "h:mm:ss") but i keep getting a #ERROR, i have tried multiple different variations and cant seem to get this to work. any suggestions?

  41. I need a small help that I am unable to convert hours into seconds as I have been trying 7:00/ 1440 for this I am getting a perfect answer but as coming to 6:30/1440 the calculator answer and this answer is not matching can you help me with this ?

  42. How can I convert days, hours, minutes, seconds to time format Hh:mm:ss with a formula? The data in excel was extracted from a database and not formatted as an actual time format.
    Example: Convert 4 days, 22 hours, 36 minutes, 58 seconds to 118:36:58

  43. hi madam,
    I have one doubt , i am key in excel my workers overtime day shift time in 6.00 out 19.30 i use formula =((19.30-06.00)-9) 4.30 , 9 meaning company hours the rest overtime this is good result , but night shift how to use formula, in 18.00 out 08.00 , please madam can you explain me formula.

  44. Hi Svetlana,
    Good day!
    Could you please let me know how to convert 1/2/1900 2:57:10 AM data format to HH:MM:SS. I have an excel full of different types of values (12:15:34 AM, 1/2/1900 2:57:10 AM). Any help would be appreciated. Thanks

  45. IF 17:30PM-9:00AM=8:30 HOURS OR 16:30PM-9:30AM=7HOURS THAN HOW TO I KNOW 31 DAYS CALCULATION IN EXCEL

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

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

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

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

  50. I need to split date and time in two different cells from the below format
    2019-06-21 18.36.30

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