0

Example

09::08:00

09:09:01

I want to add semicolon like this

09::08:00;

09:09:01;

enter image description here

5
  • 1
    When i add semicolon to this cell 09:08:00 then it changes to 0.380555555555556; but i want semicolon in HHMMSS format only.
    – karthik
    May 30, 2020 at 5:57
  • If the cell format is General/Number or even Text, then you may enter 09::08::00; but remember Excel will never consider it as TIME! Please Edit your question and write the reason behind using TIME in such uncommon format,, better add Screen Shot ,, and avoid vote to close the question. May 30, 2020 at 7:37
  • added image that changed after i tried to add semicolon in time format.(i want this for back testing purpose)
    – karthik
    Jun 4, 2020 at 13:42
  • The screen shot U have added is totally different than the data you have shared with the Question !! Jun 5, 2020 at 5:40
  • Hi @karthik ,, check my post I'm sure this is what you are looking for . Jun 5, 2020 at 9:55

2 Answers 2

2

Change the format in custom format:

enter image description here

Also you could point to a cell with the time you want using the formula: =HOUR(A2)&":"&MINUTE(A2)&":"& SECOND(A2)&";"

2
  • Check the Screen Shot attached with the question and your method works for a different issue,, plzz edit the post ! Jun 5, 2020 at 5:42
  • I think the screen shot is showing the error, the format wanted is in the question... which is what is achieved in the answer above. I think. Jun 5, 2020 at 7:44
0

:Caveat:

  • The NUMBER, MILITARY TIME or even TIME written in correct format could have TWO COLON between Hours, Minutes and Seconds, and does have SEMI COLON at end also.
  • But Excel doesn't considers the TIME entered as you have at top with the question 09::08:00 as a TIME value, also if you apply any formula then get #VALUE error.

Below are methods, applies TWO COLON in between & SEMI COLON at end.

enter image description here

  • To convert Number or Military Time, into desire format the Formula in cell L101 and M101:

    =(TIME(LEFT(K101,LEN(K101)-2),RIGHT(K101,2),0))
    

N.B. This works if & when cell has 4 digits.

  • Formula in cell L106 and M106:

    =(INT(K106/10000)&":"&INT(MOD(K106,10000)/100)&":"&MOD(K106,100))+0
    

N.B. This works with more than 4 digits.

  • Cell format applied for SEMI COLON at end.

    h:mm\;

  • This may be h:mm:ss\;

  • Cell format for 2 COLON in between & SEMI COLON at end.

    h:\:mm:ss\;

  • Cell K110 has TIME value and cell format applied on cell M110 is,

    h:\:mm:\:ss\;

You may adjust cell references in the formula as needed.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .