Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Seperate Decimal & Whole Number

  1. #1
    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. #2
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,716

    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. #3
    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. #4
    Join Date
    12th September 2006
    Location
    The Great State of Texas
    Posts
    4,323

    Re: Seperate Decimal & Whole Number

    1.2 really means 1min 20s and not 1min 12s?
    Entia non sunt multiplicanda sine necessitate.
    MS MVP - Excel

  5. #5
    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


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Insert Decimal Into Whole Number
    By Lampit in forum EXCEL HELP
    Replies: 3
    Last Post: August 15th, 2008, 10:46
  2. Round Decimal Number Based On Decimal Portion
    By sk8salomon in forum EXCEL HELP
    Replies: 2
    Last Post: February 8th, 2008, 12:17
  3. Round Off Decimal Number To Whole Number
    By khenzel in forum EXCEL HELP
    Replies: 2
    Last Post: September 19th, 2007, 23:46
  4. Decimal Shown As Whole Number In Code
    By kippy in forum EXCEL HELP
    Replies: 2
    Last Post: July 22nd, 2007, 10:09
  5. Calculate Decimal Portion Of Decimal Number`
    By combsgroup in forum EXCEL HELP
    Replies: 2
    Last Post: June 11th, 2007, 12:28

Bookmarks

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