Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Unable to recollect an Excel formula

    Hi Excel Experts,

    I had sometime back used a formula that im unable to recollect. Can someone help.

    I used this formula to covert to hours and minutes to decimals

    for eg. if i had 6.30 (numeric format) this formula would convert it to 6.5.

    I could used this to convert any measurments to decimals i.e. if I had 1.6 feet where 12 inches is 1 feet the formula would convert it to 1.5.

    Also there was a formuls that works the reverese i.e. 6.5 coverted to 6.30

    Can someone help.

    Regards
    Baiju

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Are you talking about the CONVERT function?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    These assume A1 has the value
    To convert 1.3 (meaning 1:30) to decimal you can use a formula
    =INT(A1)+MOD(A1,1)/0.6

    To convert the other way:
    =INT(A1)+MOD(A1,1)*0.6

    If the number represents feet and decimal represents inches:
    =INT(A1)+MOD(A1,1)/1.2

    And to convert it back:
    =INT(A1)+MOD(A1,1)*1.2

    [Note that the feet /inches only works with <10 inches. If you will want the entire range, then 1ft and 1 inch must be 1.01 not 1.1. Your scheme means that 1ft-1 in is the same as 1ft-10in and 1ft-11in is the same as 1ft-1.1 inches]

    If you use 1.01 to represents 1ft-1in then you can use
    =INT(A4)+MOD(A4,1)/0.12

    Steve

  4. #4
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi Steve,

    Thanks for the formula. I had used the Dollarde and Dollarfr formula sometime back and was trying to recollect the same.

    Thanks for your formula, this also meets my need.

    Regards
    Baiju

Posting Permissions

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