Can someone provide a straightforward answer:
Convert 1:30 to 1.50 in Numbers. Excel is simple 1:30 *24 =1.50 (General Formula)
Convert 1:35 to 1.58
What do I put in the cell to yield the conversion? Do I need to change the format options?
It depends. I am assuming by "time" you mean "duration" (like 1 hour 30 minutes) not "time of day" (like 12/20/24 at 1:30AM). I'm not totally clear on what Excel does or how you are formatting the cell with 1:30 but it seems to do strange math if the value is 24:00 or greater. You might have to enlighten me on that if what I write below isn't helpful.
If cell B2 is text formatted and has 1:30 in it, it will be interpreted as 1 minute 30 seconds when used in one of the duration functions.
=DUR2MINUTES(B2) will turn it into a decimal 1.50
Text formatting is kind of dangerous, though, because other functions and formulas may interpret it as today as 1:30AM. For example, you cannot do =B2+B2 because it will try to add it as two dates.
If cell B2 is formatted as a duration with units of minutes and seconds, the formula is =DUR2MINUTES(B2)
If cell B2 is formatted as a duration with units of hours and minutes, the formula is =DUR2HOURS(B2)
Data entry of 1 hr 30 minutes is either done as "1h 30m" or as "1:30:00", not as "1:30" because that is 1 minute 30 seconds.
It depends. I am assuming by "time" you mean "duration" (like 1 hour 30 minutes) not "time of day" (like 12/20/24 at 1:30AM). I'm not totally clear on what Excel does or how you are formatting the cell with 1:30 but it seems to do strange math if the value is 24:00 or greater. You might have to enlighten me on that if what I write below isn't helpful.
If cell B2 is text formatted and has 1:30 in it, it will be interpreted as 1 minute 30 seconds when used in one of the duration functions.
=DUR2MINUTES(B2) will turn it into a decimal 1.50
Text formatting is kind of dangerous, though, because other functions and formulas may interpret it as today as 1:30AM. For example, you cannot do =B2+B2 because it will try to add it as two dates.
If cell B2 is formatted as a duration with units of minutes and seconds, the formula is =DUR2MINUTES(B2)
If cell B2 is formatted as a duration with units of hours and minutes, the formula is =DUR2HOURS(B2)
Data entry of 1 hr 30 minutes is either done as "1h 30m" or as "1:30:00", not as "1:30" because that is 1 minute 30 seconds.
As you've discovered, Excel and Numbers handle "time" (point-in-time, and time between points-in-time) differently. Each approach has its advantages and disadvantages. The Duration data format in Numbers, which Excel doesn't have, means you can simply subtract one time from another to get the result in hours and minutes. No modular math needed.
In Numbers I recommend entering and displaying values in the native h m s ms Duration notation rather than the ambiguous colon format, which could mean either point-in-time or difference between times.
If you already have a lot of values entered in the ambiguous colon format then you can coerce them to DURATION by adding or subtracting "0:00" as in the screenshot.
The DUR2 family of functions (which don't exist in Excel) are handy for converting from Duration to Decimal for further calculation.
SG
Convert Time in Number to decimal
