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 2. Total comments: 165

  1. how can i extract and use only the time from the now() function and then use the time in an another function

  2. I have a column with times in that I intend to sum. If a value is less than 1 hr, is there a way to format it so it displays as 0:mm?

    Thank you.

  3. How can we get the different time stamp in different cell

    e.g. when i update any number in 1st column in 1st cell the current time will get update and subsequently different time for another cell

  4. Hello,

    I need the column Say ( "D" Start Time and "F" End Time ) to automatically capture the system time when my team inputs a file number (Data) in column "A"

    Requirement :
    Column D1 should capture the start time when Data is added in Column A1
    Column F1 should capture the end time when the data is added in Column A2

      1. Hello,
        I want to read a cell value at only at 8:00 am every day.. and that only one value in that cell need to recorded every day with date. My spreadsheet sheet is in timer update value of 00:20 hh:mm.

        Example: energy meter reading. Daily at 8:00 I need to record the value in a sheet. And also need to display the yesterday consume in a seperate cell.

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

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

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

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

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

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

          1. That’s a an important learning. Can you suggest any formula that extracts the exact time only without date value?

            1. Hi!
              It is necessary to extract the fractional part from the number.

              =A2-INT(A2)

              This should solve your task.

              1. Thank you. I will try this.

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

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

    1. Hi!
      I used hh:mm:ss time format and could not repeat your problem. You may not have provided all the details.

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

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

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

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

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

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

      1. Good day Alexander,

        Thanks a million, the formula worked beautifully, doing exactly what I need.

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

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

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

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

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

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

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

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

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

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

  23. क्रमांक दिनांक नाम
    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

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

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

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

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

  28. 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).

  29. 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"

  30. 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)

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

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

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

  34. 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))

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

    1. Incase anyone comes across this same problem, you just have to add the Date before the time to solve it.

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

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

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

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

  38. DOWN_DATE DOWN_TIME CLEARED_DATE CLEARED_TIME
    15.12.2017 5:27:04 15.12.2017 8:18:38
    14.12.2017 18:34:24 14.12.2017 20:56:45
    30.12.2017 12:17:42 30.12.2017 13:28:11
    13.12.2017 7:11:34 14.12.2017 8:53:06
    15.12.2017 5:27:14 15.12.2017 8:18:12

    how to calculate diffrence of down duration .in excel 13.12.2017 7:11:34 -14.12.2017 8:53:06 showing as 1/1/1900 1:41:32 AM .which is not correct .tell me correct formula pl

    1. Hello,

      If I understand your task correctly, please try the following formula:

      =ROUNDDOWN(B1-A1,0) & " days " & TEXT(TIME(HOUR(B1-A1),MINUTE(B1-A1),SECOND(B1-A1)), "hh:mm:ss")

      Where cell A1 is “13.12.2017 7:11:34”, cell B1 is “14.12.2017 8:53:06”

      Hope this will help you!

  39. Hi - I'm trying to evaluate a planned timetable against the actual journey times. Generally straightforward (a simple a-b gives the right answer) but if my journey time took less time than scheduled (ie I'm planned to arrive at 10:15 but arrived at 10:05) how can I automatically calculate this to show a response of -00:10?

    Your guidance as ever...

    1. Hello, Tablespider,

      Please try the following formula:

      =IF(A1>=B1,A1-B1,"-"&HOUR(B1-A1)&IF(MINUTE(B1-A1)<10,":"&"0"&MINUTE(B1-A1),":"&MINUTE(B1-A1))&IF(SECOND(B1-A1)<10,IF(SECOND(B1-A1)=0,"",":"&"0"&SECOND(B1-A1)),":"&SECOND(B1-A1)))

      Hope it will help you.

  40. Hi,
    I have a column with Date and Time. I need to write a formula to show, if the date and time is 10-10-2017 12:53 PM, if time is after 2:30 PM the date to change to next date. Please advise.

    1. Hi, Austin,

      could you please specify where is that "next date" located? Should the formula just return the next date from the list or do you have an additional list?

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

    1. Use [$-F400]h:mm:ss AM/PM

      But you have to be on a 12 hr clock not 24 hr

  42. Hi How to calculate 108 hrs post from 11/07/2017, 6:00, kindly share the formula.

    Regards,
    Sowmyashree

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

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

    1. Hello, Bharat,

      this may happen if the date and time are formatted differently on your computers. Please, take a closer look at this part of the article to make sure that you apply one and the same time format on your machines.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

      A1 = Date
      A2 = Time

    2. just use =NOW() and format.

    3. CTRL+; for date
      CTRL+SHIFT+; FOR TIME

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

    4. Hello Ahmed,

      You may use CONCATENATE functions to combine those two cells.

      hope this will help

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)