General discussion

  • Creator
    Topic
  • #2099664

    Time format in excel

    Locked

    by michaeldanielwinn ·

    Does anyone know how to format a cell in excel for TIME so that we do not have to add the colon in the time I.E. 1045 for 10:45? Keep in mind that we must add up the time so doing it under the GENERAL format will not work.

All Comments

  • Author
    Replies
    • #3723429

      Time format in excel

      by mslizny ·

      In reply to Time format in excel

      The colon separates hours from minutes, even with “Date” formatting. If you want to use hours and minutes, you will have to type in the colon to separate the minutes from the hours.

    • #3723419

      Time format in excel

      by d. brinson ·

      In reply to Time format in excel

      You could try to enter it as 1045 in General format, and then convert the general format to time format to do your summation.

      =CONCATENATE(IF(LEN(A1)=4,LEFT(A1,2),LEFT(A1,1)),”:”,RIGHT(A1,2))

      will take a general text screen in A1
      and convert it to a text string
      1045 = 10:45
      945 = 9:45
      0945 = 09:45

      You can then use the value function to convert that to a decimal value which is how Excel thinks of time.

      value(A2)

      Hope it helps.

    • #3723381

      Time format in excel

      by dklippert ·

      In reply to Time format in excel

      You can further refine the formula by combining them:
      =VALUE(CONCATENATE(IF(LEN(A1)=4,LEFT(A1,2),LEFT(A1,1)),”:”,RIGHT(A1,2)))
      You will then need to format the cell as Time.

    • #3610218

      Time format in excel

      by andy1957 ·

      In reply to Time format in excel

      If you just want the time format to be HHMM without the colon, why not just “Format Cell Number Custom HHMM”? I’ve just tried this and it works.

    • #3610201

      Time format in excel

      by michaeldanielwinn ·

      In reply to Time format in excel

      This question was closed by the author

Viewing 4 reply threads