New Data Type Suggestion: Unit Value

One of the things that science/engineering/inventory spreadsheet users have been doing since the beginning is working their own unit conversions into their models. Yes, there is the CONVERT function, but that still requires input to be in specific units, and if you want to display units you either need an entire different column for listing the unit name or you have to use custom number formatting to add " kg" or " sec" to your value.

Due to the focus on low-code/no-code, my suggestion is to create a new Data Type, which I’ll call “Unit Value” for no better term for now. Here is how it might function:

Data Type Options:
a) Unit Type, with a drop-down that lists a huge range of options maybe as a categorized list or just the general category (mass, weight, volume, etc). Minimal options would include all of the units available for the CONVERT function. The value stored would be in the chosen unit, or a default base unit defined in the help system.
b) Displayed Unit Type, after choosing the unit type you would want to be able to choose which unit to show in the displayed values. The values displayed would include the unit but still be usable in calculations, just like using a custom number format in Sheets/Excel like 0.00 “kg”
c) Precision … like the # format, you could allow the user to choose the precision and an option for scientific notation (because significant digits are easier with scientific notation).

User Interface
This is where the data type would gain its wow factor. When double-clicking on a value that uses this new data type, you could open a drop-down contextual window (like the date picker) that would let you choose a unit from the drop-down so that when you enter the value, you are entering it in your chosen unit. Then, after pressing Enter, the value is automatically converted and stored based on the Data Type option (and displayed based on the Data Type’s chosen unit type).

Similar to entering a date or a time, and even more similar to entering a Duration, other functionality could be the ability to type the desired unit and have SSDC automatically convert the value to the current cell’s displayed unit. For example, if the Date Type is Weight and it is displaying “# kg”, I may want to quickly type “5 lb” and I’d want it to be smart enough to convert that to kilograms if kilograms is the unit currently being displayed in that cell. If I entered the value “5 lb 2 oz” I would want that converted to decimal pounds or whatever the displayed weight unit is.

Case Study
I’m currently working on a spreadsheet for inventory control and orders and I’ve found that tools like ShipStation/Shopify/Stamps.com make it very easy to choose whether to enter weights for products and packages in different units, and they convert the units internally as needed.

So, if I have an inventory spreadsheet and I want to enter weights, I may have some in ounces, pounds, pounds and ounces, decimal pounds, decimal ounces, kilograms, or grams. But, it’s very inconvenient to have multiple columns for these different input/display options, and it’s inconvenient to be doing these conversions outside the spreadsheet just because I’m limited to a particular unit for input. Instead, I’d like just one column to represent Weight and I’d like to be able to use the Data Type option to automatically convert those weights to whatever unit I choose to display, and I want the interface to let me enter the value in whatever weight unit is most convenient.

In essence, I guess this suggestion is an expansion on what the Duration data type was meant to be. But, instead of just Time, this data type could be used for any consistent unit of measure.

2 Likes

This is an interesting and powerful suggestion, we will consider it as part of our data types roadmap and makes sense in many use cases.

Meanwhile, stay tuned for significant improvements to the Duration data type coming soon, particularly in its ability to parse more duration unit types such as:

  • nanoseconds (ns)
  • microseconds (ÎĽs)
  • milliseconds (ms)
  • seconds (s, sec)
  • minutes (m, min)
  • hours (h, hr)
  • days (d)
  • weeks (w, wk)
  • months
  • years (y, yr)
1 Like

Excellent! I’m looking forward to the Duration improvement. I’d recommend also being able to parse the full unit name like “50 microseconds” or “1 microsecond” and “hour” or “hours” - not just the abbreviation.

You will eventually get requests to parse entry of combined durations, such as ages specified as “45 y 10 mo 32 d” or “45 years, 10 months, 32 days” - so I’d recommend a general algorithm that can parse any combination of duration units separated by spaces and/or commas (or no spaces between the numbers - like you currently have formats for) and add them together. This would mean a user could do something weird like “10 hr, 5 h, 20s 5h 30 min” but that may be better than forcing a very strict duration format. Just a suggestion. :slight_smile:

There will also be a need to handle multiple languages eventually.

I’ve done quite a bit of work with unit conversions (including online calculators), so unless you already have a source for code on that, contact me if/when you get around to playing with the data type I suggested.

2 Likes

Excellent suggestion!