How split time in other columns

[SALO]

I trying to split time that is in one cell into multiple so that I can then you formulas to get the information that I want. (sumif, countif, etc) In the cell I have them for example 9:30:04 AM . I want to split it other cells just to 30 in one cell. I the next cell :04. I tried use the =left command but it gives me weird numbers back. For example =left(J25,2) returned .39. I’m using excel 2000. If any one has some suggestion It will be much appreciated.

 

[Richard Schollar]

Hi & Welcome to the Board!

Exactly what kind of formula do you want to use? You don't need hours and minutes in separate columns to use formulas on them, so if you explain exactly what you want, we can show you how to do it with your time left complete in the one column.

 

[Norie]

Use the HOUR/MINUTE/SECOND worksheet functions.

 

[SALO]

I want to get use sumif formulas, countif formulas, average and somekind of formula to get the highest price of the stock and lowest price of the stock for certain period in minutes. (maybe dmax or dmin). I still would like to split it so I can use formulas that I am used to if possible. Any help would be appreciated.

 

[barry houdini]

Like Norie says, you could use

=HOUR(J25) to get 9
=MINUTE(J25) to get 30 and...
=SECOND(J25) to get 4

 

[arthurbr]

Cross posting

http://www.excelforum.com/showthread.php?t=651135

 

[SALO]

thanks, I tried it but gave back 12:00:00 AM
<table style="width: 393pt; border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" width="524"> <tbody><tr style="height: 14.25pt;"> <td style="border-style: solid solid solid none; border-color: silver silver silver -moz-use-text-color; border-width: 0.5pt 0.5pt 0.5pt medium; padding: 0in; width: 0.75in; height: 14.25pt;" valign="bottom" width="72"> IBM<o></o>
</td> <td style="border-style: solid solid solid none; border-color: silver silver silver -moz-use-text-color; border-width: 0.5pt 0.5pt 0.5pt medium; padding: 0in; width: 0.75in; height: 14.25pt;" x:num="39601" valign="bottom" width="72">

06/02/08<o></o>​

</td> <td style="border-style: solid solid solid none; border-color: silver silver silver -moz-use-text-color; border-width: 0.5pt 0.5pt 0.5pt medium; padding: 0in; width: 75pt; height: 14.25pt;" x:num="0.3958564814814815" valign="bottom" width="100">

9:30:02 AM<o></o>​

</td> <td style="border-style: solid solid solid none; border-color: silver silver silver -moz-use-text-color; border-width: 0.5pt 0.5pt 0.5pt medium; padding: 0in; width: 0.75in; height: 14.25pt;" x:num="128.56857081821985" valign="bottom" width="72">

128.57<o></o>​

</td> <td style="border-style: solid none; border-color: silver -moz-use-text-color; border-width: 0.5pt medium; padding: 0in; width: 0.75in; height: 14.25pt;" x:num="" valign="bottom" width="72">

150900<o></o>​

</td> <td style="border-style: none solid; border-color: -moz-use-text-color windowtext; border-width: medium 1pt; padding: 0in; background: blue none repeat scroll 0%; width: 16pt; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; height: 14.25pt;" nowrap="nowrap" valign="bottom" width="21"> <o></o>
</td> <td style="border-style: solid solid solid none; border-color: silver silver silver -moz-use-text-color; border-width: 0.5pt 0.5pt 0.5pt medium; padding: 0in; width: 86pt; height: 14.25pt;" x:num="30" x:fmla="=MINUTE(C1)" valign="bottom" width="115">

12:00:00 AM<o></o>​

</td> </tr> </tbody></table> =MINUTE(J25) GAVE ME 12:00:00

<table x:str="" style="border-collapse: collapse; width: 393pt;" border="0" cellpadding="0" cellspacing="0" width="524"><col style="width: 54pt;" span="2" width="72"><col> <tbody><tr><td class="xl70" style="height: 14.25pt; width: 54pt;" height="19" width="72">IBM
</td> <td class="xl67" style="border-left: medium none; width: 54pt;" x:num="39601" width="72">06/02/08
</td> <td class="xl66" style="border-left: medium none; width: 75pt;" x:num="0.3958564814814815" width="100">9:30:02 AM
</td> <td class="xl65" style="border-left: medium none; width: 54pt;" x:num="128.56857081821985" width="72">128.57
</td> <td class="xl68" style="border-left: medium none; width: 54pt;" x:num="" width="72">150900
</td> <td class="xl69" style="width: 16pt;" width="21">
</td> <td class="xl66" style="width: 86pt;" x:num="30" x:fmla="=MINUTE(C1)" width="115">12:00:00 AM
</td> </tr></tbody><col style="width: 75pt;" width="100"><col style="width: 54pt;" span="2" width="72"><col style="width: 16pt;" width="21"><col style="width: 86pt;" width="115"><tr style="height: 14.25pt;" height="19"></tr></table>

 

[ScousePete]

thanks, I tried it but gave back 12:00:00 AM
<TABLE style="WIDTH: 393pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=524 border=0><TBODY><TR style="HEIGHT: 14.25pt"><TD style="BORDER-RIGHT: silver 0.5pt solid; PADDING-RIGHT: 0in; BORDER-TOP: silver 0.5pt solid; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 0.75in; PADDING-TOP: 0in; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 14.25pt" vAlign=bottom width=72>IBM<?xml:namespace prefix = o /><o></o>

</TD><TD style="BORDER-RIGHT: silver 0.5pt solid; PADDING-RIGHT: 0in; BORDER-TOP: silver 0.5pt solid; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 0.75in; PADDING-TOP: 0in; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 14.25pt" vAlign=bottom width=72 x:num="39601">

06/02/08<o></o>​

</TD><TD style="BORDER-RIGHT: silver 0.5pt solid; PADDING-RIGHT: 0in; BORDER-TOP: silver 0.5pt solid; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 75pt; PADDING-TOP: 0in; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 14.25pt" vAlign=bottom width=100 x:num="0.3958564814814815">

9:30:02 AM<o></o>​

</TD><TD style="BORDER-RIGHT: silver 0.5pt solid; PADDING-RIGHT: 0in; BORDER-TOP: silver 0.5pt solid; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 0.75in; PADDING-TOP: 0in; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 14.25pt" vAlign=bottom width=72 x:num="128.56857081821985">

128.57<o></o>​

</TD><TD style="BORDER-RIGHT: medium none; PADDING-RIGHT: 0in; BORDER-TOP: silver 0.5pt solid; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 0.75in; PADDING-TOP: 0in; BORDER-BOTTOM: 0.5pt solid; HEIGHT: 14.25pt" vAlign=bottom width=72 x:num="">

150900<o></o>​

</TD><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: medium none; PADDING-LEFT: 0in; BACKGROUND: blue 0% 50%; PADDING-BOTTOM: 0in; BORDER-LEFT: 1pt solid; WIDTH: 16pt; PADDING-TOP: 0in; BORDER-BOTTOM: medium none; HEIGHT: 14.25pt; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial" vAlign=bottom noWrap width=21><o></o>

</TD><TD style="BORDER-RIGHT: silver 0.5pt solid; PADDING-RIGHT: 0in; BORDER-TOP: silver 0.5pt solid; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 86pt; PADDING-TOP: 0in; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 14.25pt" vAlign=bottom width=115 x:num="30" x:fmla="=MINUTE(C1)">

12:00:00 AM<o></o>​

</TD></TR></TBODY></TABLE>=MINUTE(J25) GAVE ME 12:00:00

<TABLE style="WIDTH: 393pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=524 border=0 x:str=""><COLGROUP><COL style="WIDTH: 54pt" span=2 width=72><COL><TBODY><TR><TD class=xl70 style="WIDTH: 54pt; HEIGHT: 14.25pt" width=72 height=19>IBM

</TD><TD class=xl67 style="BORDER-LEFT: medium none; WIDTH: 54pt" width=72 x:num="39601">06/02/08

</TD><TD class=xl66 style="BORDER-LEFT: medium none; WIDTH: 75pt" width=100 x:num="0.3958564814814815">9:30:02 AM

</TD><TD class=xl65 style="BORDER-LEFT: medium none; WIDTH: 54pt" width=72 x:num="128.56857081821985">128.57

</TD><TD class=xl68 style="BORDER-LEFT: medium none; WIDTH: 54pt" width=72 x:num="">150900

</TD><TD class=xl69 style="WIDTH: 16pt" width=21>

</TD><TD class=xl66 style="WIDTH: 86pt" width=115 x:num="30" x:fmla="=MINUTE(C1)">12:00:00 AM

</TD></TR></TBODY><COLGROUP><COL style="WIDTH: 75pt" width=100><COL style="WIDTH: 54pt" span=2 width=72><COL style="WIDTH: 16pt" width=21><COL style="WIDTH: 86pt" width=115><TBODY><TR style="HEIGHT: 14.25pt" height=19></TR></TBODY></TABLE>

Format the formula cell (not J25) to be a number, not time and you will find it will work correctly.