Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 13 of 13

Thread: Time

  1. #11
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    My last guess:

    =SUMPRODUCT(INT(A2:F2)+100*(A2:F2-INT(A2:F2))/60)

  2. #12
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-02-24 17:30, StacyRenee79 wrote:
    Yeah I can do that but anything over 60 min. I need 40 added to that, in order for the value to be accurate.

    Ex. 9.50 + 9.50 = 19.00 but time wise you have to add 40 for the correct hours. You can't add 50min. and 50min. to get 100min. It's actually 1 hour 40 min.
    Stacey,

    What I believe you should do is use the : as a separater rather than the point your using currently.

    This will tell Excel that the value is TIMEVALUE() and the sum of 9:50 + 9:50 will result in 19:40. Also bear in mind that Excel will assume that anything over 24hours is a new day so 23:00 + 2:00 will be 1:00, so you'll need to change to custom format [h]:mm as suggested by Aladin.

    if you still want to use general numbers like 9.5 but want the answer as TIMEVALUE I'd go with Aladin's formula but slightly ammended:

    =(SUMPRODUCT(INT(A2:F2)+100*(A2:F2-INT(A2:F2))/60))/24

    and use the same custom format on the cell.

    If you don't use the /24 at the end it will return the answer as a decimal value so 9.5 is 9:30, (also a point made by aladin).

    Time can get very confusing at first but keep pluging away and it'll all make sense.

    keep it up

    Ian Mac

  3. #13
    New Member
    Join Date
    Feb 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It worked THANK YOU!!!!!!

Some videos you may like

User Tag List

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
  •