+ Reply to Thread
Results 1 to 7 of 7

format issue

  1. #1
    Forum Contributor
    Join Date
    04-04-2016
    Location
    dallas tx
    MS-Off Ver
    2010
    Posts
    283

    format issue

    I am having cell formatting issues.
    first lets do the math. I have 77 items and it takes 2.7 minutes to move one of them from point a to point b.
    Doing the math it will take 207.90 total minutes to move all 77 pieces. 77 is in cell A1 and 2.7 is in Cell A2.
    The formula I am using is
    Please Login or Register  to view this content.
    . The result is 207.90 in cell A3. What I need is to show Days:hours:minutes in cell A3 so that the result is 00:03:27. o days:3 hours: 27 minutes. I have tried custom formatting with dd:hh:mm and it does not give me the result that I need. Can't figure out what I am doing wrong.

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,364

    Re: format issue

    what you need to understand about dates/times in Excel is that they are stored as day.fraction of day (recommend this if you are unfamiliar with how Excel stores dates/times: http://www.cpearson.com/Excel/datetime.htm ). All you should need to do is convert your 2.7 minutes into a fraction of a day 2.7/24/60. So something like =A1*A2/24/60 formatted as dd:hh:mm should work. The only caveat I would add is that this is not a true elapsed days format, so if your result will ever be greater than about 31 days (the number of days in January), the format will fail to display correctly. As long as your result will always be less than 31 days, then this should work just fine.
    Last edited by MrShorty; 08-08-2018 at 12:59 PM.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,667

    Re: format issue

    Try this:

    =(A1*A2)/1440
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,575

    Re: format issue

    If you want to have the words "days", "hours" and "minutes" in your result, then you will have a text value - you won't be able to use this for further calculations if required.

    Hope this helps.

    Pete

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,575

    Re: format issue

    But, just in case you want it like that, you can use this in A3:

    =INT((A1*A2)/24/60)&" days "&MOD(INT((A1*A2)/60),24)&" hours "&ROUNDDOWN(MOD(A1*A2,60),0)&" mins"

    Hope this helps.

    Pete

  6. #6
    Forum Contributor
    Join Date
    04-04-2016
    Location
    dallas tx
    MS-Off Ver
    2010
    Posts
    283

    Re: format issue

    That solved it. Thank you!

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,667

    Re: format issue

    What solved it?

    Glad to have helped, anyway.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 4
    Last Post: 04-29-2015, 08:36 AM
  2. [SOLVED] CSV Format Issue
    By djfatboyfats in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-22-2014, 02:38 PM
  3. [SOLVED] .csv format issue
    By ccampbell14 in forum Excel General
    Replies: 2
    Last Post: 11-05-2013, 05:52 PM
  4. Format issue
    By Irina in forum Excel General
    Replies: 5
    Last Post: 04-30-2009, 04:18 PM
  5. Is this a format issue?
    By ExcelNewby in forum Excel General
    Replies: 6
    Last Post: 02-22-2009, 01:44 PM
  6. format issue
    By farfromapro in forum Excel General
    Replies: 17
    Last Post: 12-18-2006, 01:01 PM
  7. # format issue
    By Cip in forum Excel General
    Replies: 3
    Last Post: 03-31-2006, 12:55 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1