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 8. Total comments: 271

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    1. Hi Ali,

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

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

      2. Hi madam,

  25. 48 hours example

    1. Hi Anant,

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

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