Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 14

Thread: Convert Number To Hours & Minutes

  1. #1
    Join Date
    9th June 2004
    Location
    England
    Posts
    1,047

    Convert Number To Hours & Minutes

    How can I convert an ordinary number i.e. 3.45 to Hours & Minutes?

    A colleague has columns of data that when summed are all wrong i.e. 8.65 instead of 9 Hours 5 minutes.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    7th December 2005
    Location
    Hampshire, England
    Posts
    4,898

    Re: Convert Number To Hours & Minutes

    Not much to go on, Timbo. Are we talking 3.45 decimal, 3.45 hours, or 3.45 days? See Excel Dates & Times.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    9th June 2004
    Location
    England
    Posts
    1,047

    Re: Convert Number To Hours & Minutes

    Formatted as a number to two decimal places.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    7th December 2005
    Location
    Hampshire, England
    Posts
    4,898

    Re: Convert Number To Hours & Minutes

    Sample workbook please, then...

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    9th June 2004
    Location
    England
    Posts
    1,047

    Re: Convert Number To Hours & Minutes

    See attached.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    7th December 2005
    Location
    Hampshire, England
    Posts
    4,898

    Re: Convert Number To Hours & Minutes

    OK, Assuming these are hours, so 3.45 needs to show as 3:27, you need to divide by 24, and format as Custom [h]:mm

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    9th June 2004
    Location
    England
    Posts
    1,047

    Re: Convert Number To Hours & Minutes

    Why 3.27? 3.45 represents 3 hours 45 minutes.

    In the sample workbook that I attached and said earlier 8.65 should equal 9 hours 5 minutes.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    25th May 2004
    Location
    central Europe
    Posts
    773

    Re: Convert Number To Hours & Minutes

    why not enter the times in the right format to begin with and write 3:45? this way xl will automatically assume it's a time. 8:65 however will not be recognized. That really does not make sense.
    In formulae, depending on your locale, you might have to replace ; with , or vice versa.

  9. #9
    Join Date
    13th September 2003
    Location
    Singapore
    Posts
    1,236

    Re: Convert Number To Hours & Minutes

    Quote Originally Posted by Timbo
    A colleague has columns of data that when summed are all wrong i.e. 8.65 instead of 9 Hours 5 minutes.
    hi Timbo,

    what you should do is correct the mistake yr colleague did before summing them up

    try this formula to convert them to time
    then you should be able to get the correct sum:

    =SUBSTITUTE(A1,".",":")

    where A1 is the wrong entry.

    hth
    Cheers
    ___________
    Xlite
    All you need to learn VBA is an internet connection and Ozgrid.com

  10. #10
    Join Date
    7th December 2005
    Location
    Hampshire, England
    Posts
    4,898

    Re: Convert Number To Hours & Minutes

    Try this, applied to A12:

    =(INT(A12)*60+MOD(A12,1)*100)/60/24

    and format as Custom [h]:mm

    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 Minutes Into Decimal Hours
    By barbitch in forum EXCEL HELP
    Replies: 9
    Last Post: August 29th, 2007, 17:59
  2. Convert Hours & Minutes To Minutes `
    By liz curtis in forum EXCEL HELP
    Replies: 13
    Last Post: July 5th, 2007, 09:49
  3. Replies: 3
    Last Post: December 5th, 2006, 01:50
  4. Convert number to days - hours - minutes
    By rsikin in forum Excel and/or Access Help
    Replies: 6
    Last Post: November 9th, 2004, 04:51
  5. convert hours to minutes
    By Melis in forum EXCEL HELP
    Replies: 4
    Last Post: September 29th, 2004, 05:05

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