+ Reply to Thread
Results 1 to 3 of 3

Time value conversion

Hybrid View

  1. #1
    Registered User
    Join Date
    12-22-2010
    Location
    Slovakia
    MS-Off Ver
    Excel 2010
    Posts
    20

    Time value conversion

    Hi,

    in one collumn i have different time values (hr, min, sec ...), which id like to convert to the same- to have all in minutes. How can i do that..? Im sending picture what it looks like...

    Thank you.

    IB
    Attached Images Attached Images

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Time value conversion

    Sample Excel files are generally better than images - also it helps if you detail some expected results - it's not clear if you want the results as Time values or as Decimal minutes ?

    If you want result as genuine Time value

    C2:
    =LEFT(B2;FIND(" ";B2)-1)/VLOOKUP(REPLACE(B2;1;FIND(" ";B2);"");{"sec"\86400;"min"\1440;"hr"\24};2;0)
    copied down
    where original entry in B2, format result cells as [m]:ss

    If you want Decimal Minutes - as above but multiply result by 1440

    C2:
    =(LEFT(B2;FIND(" ";B2)-1)/VLOOKUP(REPLACE(B2;1;FIND(" ";B2);"");{"sec"\86400;"min"\1440;"hr"\24};2;0))*1440
    copied down
    where original entry in B2, format result cells as Number to 2 Decimals

    Above is assuming Slovak configuration re: delimiters

  3. #3
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Time value conversion

    Or use a macro for the whole range:

    Sub snb()
      [B1:B100] = [if(A1:A100="","",left(A1:A100,find(" ",A1:A100)-1)*choose(find(right(A1:A100,1),"rnc"),60,1,1/60))]
    End Sub



+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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