Convert ounces to pounds and ounces

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Bigdaddrock
    New Member
    • May 2010
    • 67

    Convert ounces to pounds and ounces

    I have an invoice that lists the various products' weights in ounces. My client would then like to total those amounts (no big deal), but then post the total amount in the format of "X Lbs and Y Oz".
    Can anyone come up with a means of deriving such text items? By the way, he would like to reflect the ounces to the first decimal point (each of the items carries their weight to a single digit).
    Thanks for the assistance.
  • Mariostg
    Contributor
    • Sep 2010
    • 332

    #2
    Knowing there are 16 ounces in a pound:
    Code:
    oz = 8
    lb = Int(oz / 16)
    oz = oz Mod 16
    answer = lb & " lbs and " & oz & " ounces"
    Sample above returns answer = 0 lbs and 8 ounces

    Comment

    • OldBirdman
      Contributor
      • Mar 2007
      • 675

      #3
      I know there are 16 Avoirdupois ounces in a pound, 12 Troy ounces.
      A condition here is "By the way, he would like to reflect the ounces to the first decimal point (each of the items carries their weight to a single digit)." So we have partial ounces.
      Code:
      oz = 42.7
      lb = Int(oz / 16)  'Use 12 if Troy ounces
      oz = oz - (lb * 16) 'Mod Function returns an integer     'Use 12 if Troy ounces
      answer = lb & " Lbs and " & Format(oz,"0.0") & " Oz"  'To ALWAYS show Oz to 1 decimal place, even if whole number
      '2 Lbs and 10.7 Oz

      Comment

      • Mariostg
        Contributor
        • Sep 2010
        • 332

        #4
        OldBirdMan, you are eagle eye but I am sure so does the OP.

        Comment

        • Bigdaddrock
          New Member
          • May 2010
          • 67

          #5
          That was a great solution!! For others reading this thread, I placed the three lines of code (shown as 2,3,and 4) into a query where I had the total ounces to be converted. It works perfectly.
          Thanks again!!

          Comment

          Working...