Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Convert Decimal Times to Hours & Minutes

  1. #1
    Join Date
    28th October 2005
    Posts
    58

    Convert Decimal Times to Hours & Minutes

    Hi,

    I need to take a range of times in decimal format (i.e seven and a half hours is 7.5) and separate the hours and the minutes into 2 columns and have them display properly so 7.5 hours would show as 7 hours in one column and 30 minutes in another. I cannot for the life of me find any explanation of how to do this either in the forum or the internet in general.

    Many thanks

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    28th September 2005
    Location
    UK
    Posts
    60

    Re: Separating values into different columns

    Yo Salparadise

    Welcome to Ozgrid. Great place to be.

    Have you tried Data/Text To Columns.

    Select the column or cells (no more than one column wide) and select text to columns under the Data menu.
    Use Delimited option and select Other and put . in the box. This will split the dat in to two columns.

    Laters
    Sweeeet

  3. #3
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    18,140

    Re: Separating values into different columns

    This wil creturn the part hour as minutes- the time (7.5) is in A3
    =MOD(A3*60,60) & " minutes"
    This will return whole hours
    =INT(A3)&" hours"
    Last edited by royUK; October 28th, 2005 at 18:02.
    Hope that Helps

    Roy

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

  4. #4
    Join Date
    14th September 2004
    Location
    Surrey, England
    Posts
    802

    Re: Separating values into different columns

    SalParadise

    If the hours are stored as decimal in A1

    in B1 type =Trunc(A1)
    in C1 type =60*(A1-B1)

    Alan.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,713

    Re: Separating values into different columns

    Hi salparadise

    Welcome to ozgrid

    Lets say these decimal times are in A1 onwards, in B1 Enter:
    =INT(A1)/24
    and format the cell as hh:mm

    Now, in C1 Enter:
    =MOD(A1,1)/24
    and custom format the cell as [m]

    Now copy down. You can convert these to values only via Copy Edit>Paste Special - Values
    Last edited by Dave Hawley; October 28th, 2005 at 18:04.

  6. #6
    Join Date
    28th October 2005
    Posts
    58

    Re: Separating values into different columns

    I appreciate the welcomes and quick responses!
    I think i've got it working how I want now thanks to you lot.

    Cheers
    salP

    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. Convert Mm:ss To Decimal Minutes
    By cb50chevy in forum EXCEL HELP
    Replies: 4
    Last Post: July 31st, 2008, 21:21
  2. Convert Minutes Into Decimal Hours
    By barbitch in forum EXCEL HELP
    Replies: 9
    Last Post: August 29th, 2007, 17:59
  3. Convert Hours & Minutes To Minutes `
    By liz curtis in forum EXCEL HELP
    Replies: 13
    Last Post: July 5th, 2007, 09:49
  4. Replies: 5
    Last Post: December 5th, 2006, 07:41
  5. Replies: 3
    Last Post: December 5th, 2006, 01:50

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