Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Time and Duration

  1. #1
    New Member
    Join Date
    Sep 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Time and Duration

    First, a Disclaimer. I am absolutely 100% new to working with Excel. This exercise I am having an issue with is my first time playing with cell formatting and formulas.

    What I want to do, which sounds simple in my head, is input an amount of time into a cell, but have it display as its decimal equivalent. So, if I put 2 hours 30 minutes into cell A2 in the format of '2:30', I would like it to convert that to '2.50' and display it in cell A2. The reasons I am looking for this to happen, is so the displayed 2.50 can be used in other equations throughout the sheet without this funky time thing Excel seems to have.

    I am sorry if this question has been answered already. I did try searching, but I must be using wrong keywords. Any advice is greatly appreciated.

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,042
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Time and Duration

    Hi & welcome to MrExcel.
    Changing the format of the cell, does not change the underlying value. Therefore any formulae looking at A2 will see 2:30 not 2.5
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    New Member
    Join Date
    Sep 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Time and Duration

    Hmmm. That's a stinker. Is there some way I can have a formula that converts the time to a decimal and outputs to the same cell? I think I tried to multiply the time by 24, but it would not work for the same cell. Is there a different work around that I can try?

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,042
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Time and Duration

    A cell can have a formula or a value, but not both. You would need to use VBA to do that, although sorting out the correct formulae would probably be a better option.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    Board Regular
    Join Date
    Jul 2014
    Location
    Memphis, TN
    Posts
    2,673
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Time and Duration

    In Excel, the TIME portion of a date/time value is the decimal part of a number representing the number of days since "Jan 0, 1900"
    So any conversions must consider that in the calculation/conversion.
    So yes, multiply by 24 (hours). Remember that you may need to convert back down for certain output too.
    Excel 2013, 2016 with PowerBI
    Knowing that it can be done is half the battle!

  6. #6
    New Member
    Join Date
    Sep 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Time and Duration

    I guess the easiest way is to just use a calculator to figure the decimal equivalent of the minutes and enter the values as numbers. The easy ones like 15 minutes or 30 minutes would be great if its all I had. Sometimes however, I get oddball stuff like 22 minutes, or 39 minutes. The hours would stay the same, regardless. I did try the special paste option where I entered 24 into a blank cell, copied the cell to the clipboard and special pasted it with value and multiply checked into the cell I needed it in. It worked perfectly. The entire row populated with the correct data. But it only worked once. Perhaps I missed something there? I didn't confirm or save the special paste in the right manner? Thanks for the advice. If nothing else, it is steering me in another direction.

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,042
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Time and Duration

    If you need to have the time in decimal format, why not just a helper column with
    =A2*24
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  8. #8
    New Member
    Join Date
    Sep 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Time and Duration

    Helper column? I will have to look into that. Not sure how that would work... Hmm, got my brain ticking though. I may be able to use that and still keep what I was looking for aesthetically. Thanks

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,042
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Time and Duration

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  10. #10
    New Member
    Join Date
    Sep 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Time and Duration

    Adding a helper column at somewhere at after the last column with that formula, then hiding the column worked perfectly. Did exactly what I was looking for. Thank you folks your help and advice.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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