In Excel sheet I want to add semicolon to a cell that time format is HH MM SS

Example

09::08:00

09:09:01

I want to add semicolon like this

09::08:00;

09:09:01;

enter image description here

2 Answers

Change the format in custom format:

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

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

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