Custom Time Format Not Recognized as Number in Spreadsheet

Other Questions Google Sheets
1

I’m working on a spreadsheet for racing game lap times. The times are in m:ss.000 format like 1:17.489. I made a custom number format for this.

When I try to subtract two times using =A2-B2, I get a #VALUE! error. It says the function can’t use text as a number.

I don’t want to change the times to just seconds. I tried =VALUE(A2)-VALUE(B2) but got the same error.

Why isn’t my format working as a number? Is there a way to make the spreadsheet understand this format? Or can I make it turn 1:17.489 into 77.489 without changing how it looks?

I’ve only tried these two formulas with my custom format and the default duration format. Neither worked. Any ideas on how to fix this?

2

I encountered a similar issue when working with custom time formats. The problem appears to stem from Excel interpreting the m:ss.000 format as text rather than a numeric value.

A workaround that worked for me was to use the SUBSTITUTE function to replace the colon with a decimal point, then convert the resulting text to a number. For example, you could use:

=VALUE(SUBSTITUTE(A2, “:”, “.”)) - VALUE(SUBSTITUTE(B2, “:”, “.”))

This approach allows Excel to perform calculations while you maintain your custom display format. Adjust the cell references as needed for your particular setup.

3

hey whisperingwind, i feel ur pain! had similar issues b4. try this:

=TIMEVALUE(A2)-TIMEVALUE(B2)

it should convert ur custom format to a number excel can use. if that doesnt work, maybe try splitting the minutes and seconds into separate columns first. good luck!

4

As someone who’s dealt with this frustration before, I can offer a solution that’s worked well for me. Instead of trying to make Excel recognize your custom format directly, you can use a helper column to convert the times into a calculable format.

Try this formula in a separate column:

=MINUTE(A2) + SECOND(A2)/60 + RIGHT(A2,3)/60000

This breaks down the time into minutes, seconds, and milliseconds, then combines them into a decimal number. You can then use these helper columns for calculations while keeping your original format for display.

For subtracting times, use the helper columns:

=C2-D2 (assuming C2 and D2 are your helper columns)

This approach maintains your desired display format while allowing accurate calculations. It’s a bit more setup, but it’s saved me countless headaches in similar situations.