Example
09::08:00
09:09:01
I want to add semicolon like this
09::08:00;
09:09:01;
Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.
Sign up to join this communityExample
09::08:00
09:09:01
I want to add semicolon like this
09::08:00;
09:09:01;
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:
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.
General/Number or even Text
, then you may enter09::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.