Format / Formula to convert Time to Text

[LraeSinned]

<TABLE style="WIDTH: 180pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=239 border=0 x:str><COLGROUP><COL style="WIDTH: 116pt; mso-width-source: userset; mso-width-alt: 5632" width=154><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3108" width=85><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 116pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=154 height=17>Military time</TD><TD class=xl22 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 64pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" align=right width=85 x:num="0.20833333333333334">5:00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>General Format</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" align=right x:num="0.20833333333333334" x:fmla="=B1">0.208333333</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="' * 8540000 (no decimals)"> * 8540000 (no decimals)</TD><TD class=xl23 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" align=right x:num="1779166.6666666667" x:fmla="=B2*8540000">1779167</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>How do I get this?</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" align=right x:num>500</TD></TR></TBODY></TABLE>

Need some help with formatting/formula. For a given time (using military format) I need to create variables for the 3rd and 4th values listed above.

3rd was easy. I used an intermediary field that just shows the time in text format (general also does the same thing). That decimal value multiplied by 8540000 gives me the 3rd number.

Now I need to convert the time into a text field that strips the colon (":").

I thought this would be easy but not getting anywhere fast.

Thanks for looking.

 

[Jonmo1]

Perhaps

=SUBSTITUTE(TEXT(A1,"h:mm"),":","")*1

 

[mikerickson]

How about the custom format "hmm" (no quotes).

 

[Scott Huish]

=TEXT(A1,"hmm")

 

[Jonmo1]

=TEXT(A1,"hmm")

Duh, much better!!

 

[LraeSinned]

=TEXT(A1,"hmm")

Exactly what I was looking for. Works great. Thanks!!

Dennis

 

[siddharthchari]

This helped me add a new feature on project for Excel. Realy f