Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Seperate Decimal & Whole Number

1. I agreed to these rules
Join Date
21st September 2007
Posts
8

## Seperate Decimal & Whole Number

How do I seperate 1.2 and 1.02 miutes from the same column into Minutes and Seconds Correctly.

How do I get the first to seperate as (1) Min (20) Secs and the next to
seperate to (1) Min (2) Secs

I have 200,000 line of data in one column with times from .o1 mins to 1498.40 mins that I am doing this to. I need to convert it all back to seconds.

Short and sweet I hope

Gary

Excel Video Tutorials / Excel Dashboards Reports

2. ## Re: Seperate Time Correctly

Times are NOT stored as decimals, so you don't have times, simply decimals.

In B1
=INT(A1)
In C1
=A1-B1

Copy down via double click on the Fill Handle.

3. Banned
Join Date
18th September 2005
Location
Hampshire, UK
Posts
1,278

## Re: Seperate Decimal & Whole Number

Hi Gary

If you want to convert to seconds then maybe:

=INT(A1)*60+MOD(A1,1)*100

Richard

Excel Video Tutorials / Excel Dashboards Reports

4. ## Re: Seperate Decimal & Whole Number

1.2 really means 1min 20s and not 1min 12s?

5. Banned
Join Date
18th September 2005
Location
Hampshire, UK
Posts
1,278

## Re: Seperate Decimal & Whole Number

The formula I posted initially will do what you ask, although the resulting number will be a decimal representation of time rather than Excel time. If you want it in Excel time then:

=INT(A2)/1440+MOD(A2,1)/864

for A2 copied down.

Richard

Excel Video Tutorials / Excel Dashboards Reports

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno