Divide time Column 1

[MikeKohler]

Hi, I have a column that contains numbers in the format mm:ss. Is it possible to split the column based on the : so that mm is in one column and ss is in another?
Thank you,
Michael Kohler

 

[AnotherHiggins]

First question: Why?

Times tend to be much more useful if stored as times.

That having been said, you can simply do a TextToColumns with the colon as the delimiter. Choose not to import the first column (which will be hours).

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[MikeKohler]

Thanks for the reply. I was using faulty logic, what I am actually tring to do is split a column containing inches into 2 that contain feet and inches. I read a post about displying a number in the time format, and didn't think it through, the date had nothing to do with what I wanted, except for displaying a number in 2 parts.

 

[SkipVought]

Hi,

If you enter feet and inches as [yy]ft:in[/tt], Excel, very helpfully, CONVERTS your text, to DAYS and formats the data as time, as it assumes that you entered [tt]hrs:min[/tt]




Skip,

_[red][/red]

 

[MikeKohler]

Thanks Skip, but what happens when the data has already been entered into a spreadsheet? I am creating a new acccess database that is used to monitor the beluga whale. Old data is in an Excel spreadsheet, and various measurement columns are in inches. I divided each colum by 12 to get feet. Now is there a way to spit the new column into feet and inches?
Michael Kohler

 

[SkipVought]

nDimension = 7.3    'in feet
iFeet = Int(nDimension)
nInches = (nDimension - iFeet) * 12 Mod 12
MsgBox nInches

Skip,

_[red][/red]

 

[Rofeu]

Hi,

Am I understanding you correctly if I say, that you want to split the whole number from the decimals and vice versa?

i.e. 500 Inches = 500 / 12 = 41.6666...

so one column has the 41 and the other the 0.6666....?

If so you can use the formulae

=INT(Cell_Ref) to get the 41 and
=MOD(Cell_Ref,INT(Cell_Ref)) to get the 0.6666....


Cheers,

Roel

 

[SkipVought]

...oops, wrong language

Roel got it.

Skip,

_[red][/red]

 

[MikeKohler]

One addition, the method I use makes sense for the feet column but not for the inches. So what I ended up doing was K6-(M6*12) K6 is the original column in inches - the feet column (INT(Cell_Ref)*12. That way you get the inches.