NWildblood
Technical User
Hi, any help gratefully received: I am working in imperial (Miles/Yards) and have found that it is not possible create a custom number format that will deal with imperial linear distances: yards and miles. Put simply, I need to determine the difference in Miles and Yards between e.g.
Mileage Work Order
MM.YYYY
25.1727
Mileage System
MM.YYYY
24.0880
There are 1760 Yards in a Mile.
I want to display the difference as
1.0847 (1 mile and 847 Yards)
Workings: 24.0880 + 0880 = 25.000
1727 + 0880 = 2607 Yards
= 1.0847
I have found a custom function that converts a value into a linear measurement and display it as text, which is potentially a really useful lead to a sollution:
_________________________________
Function N2MYFI(x) As String
Dim x1 as Long
Dim FinalAnswer As String
x1 = x
Distances = Array(63360, 36, 12, 1)
FinalAnswer = ""
For Each Item In Distances
FinalAnswer = FinalAnswer & " " & Int(x1 / Item)
x1 = x1 - Item * Int(x1 / Item)
Next
N2MYFI = Trim(FinalAnswer)
End Function
_____________________________________________
This function returns four numbers, in a string, that represent the number of miles, yards, feet, and inches (MYFI) in a raw value. It is assumed that the value fed to the function is in inches, such as the following:
=N2MYFI(100)
This returns the string "0 2 2 4", which means there are 0 miles, 2 yards, 2 feet, and 4 inches in 100 inches. I can work with this sort of output, and the function works fine, however, I cannot seem to redesignate the array to work in combinations of Miles and Yards.
Ideal format will be Miles.Yards - to four points - as 1 mile = 1760 yards
Any pointers would be *really* helpful !
Thank you
No hay nada como un buitre cabr=n como un ojo de cristal.
Mileage Work Order
MM.YYYY
25.1727
Mileage System
MM.YYYY
24.0880
There are 1760 Yards in a Mile.
I want to display the difference as
1.0847 (1 mile and 847 Yards)
Workings: 24.0880 + 0880 = 25.000
1727 + 0880 = 2607 Yards
= 1.0847
I have found a custom function that converts a value into a linear measurement and display it as text, which is potentially a really useful lead to a sollution:
_________________________________
Function N2MYFI(x) As String
Dim x1 as Long
Dim FinalAnswer As String
x1 = x
Distances = Array(63360, 36, 12, 1)
FinalAnswer = ""
For Each Item In Distances
FinalAnswer = FinalAnswer & " " & Int(x1 / Item)
x1 = x1 - Item * Int(x1 / Item)
Next
N2MYFI = Trim(FinalAnswer)
End Function
_____________________________________________
This function returns four numbers, in a string, that represent the number of miles, yards, feet, and inches (MYFI) in a raw value. It is assumed that the value fed to the function is in inches, such as the following:
=N2MYFI(100)
This returns the string "0 2 2 4", which means there are 0 miles, 2 yards, 2 feet, and 4 inches in 100 inches. I can work with this sort of output, and the function works fine, however, I cannot seem to redesignate the array to work in combinations of Miles and Yards.
Ideal format will be Miles.Yards - to four points - as 1 mile = 1760 yards
Any pointers would be *really* helpful !
Thank you
No hay nada como un buitre cabr=n como un ojo de cristal.