The 1-Second Excel Bug That Taught Me About Floating-Point Precision
You probably know that many systems allow users to import data from Excel instead of creating everything manually. We had a similar feature in our application.
One of the columns in the import file was Start Time. However, we noticed something strange:
A session that started at 10:00 AM in Excel was being stored as 09:59:59 AM in our system for some rows.
It was just a one-second difference, but when clients imported hundreds of sessions, correcting those timestamps one by one became frustrating and time-consuming.
So the question was:
If Excel is correct, and our conversion logic is correct, where is the bug coming from?
How Excel Actually Stores Time
Excel does not store time as 10:00:00.
Instead, it stores time as a fraction of a 24-hour day.
1 represents 24 hours
One hour = 1 / 24 = 0.041666667
One minute = 1 / (24 × 60) = 0.000694444
One second = 1 / (24 × 60 × 60) = 0.00001157407
12 hours = 12 / 24 = 0.5
24 hours = 24 / 24 = 1
So:
10:00:00 is stored as 0.41666666667
09:59:59 is stored as 0.41665509259
This is perfectly valid. Excel’s math is correct.
Our Backend Conversion Logic
On our backend (Rails), we were converting the imported value like this:
time = Time.at(value.to_f).utc
Under the hood, this effectively does:
seconds = value * 86400
Since there are 86,400 seconds in a day, multiplying the decimal by 86,400 gives the number of seconds.
This logic is also mathematically correct.
So if both systems are correct then why the one-second difference?
The Real Problem: Floating-Point Representation
The issue wasn’t incorrect math.
It was floating-point representation.
Excel stores time as floating-point numbers. Ruby also uses floating-point numbers when you call to_f.
The problem is that many decimal numbers cannot be represented exactly in binary floating-point format.
For example:
0.41666666667
cannot be stored exactly in binary. Internally, it becomes something like:
0.416666666666657...
When you multiply that slightly imprecise value by 86400, you don’t always get:
36000 seconds
You might get:
35999.9999998
And when Ruby converts that to an integer number of seconds, it truncates it.
So instead of 36000 seconds (10:00:00), you get:
35999 seconds → 09:59:59
That’s your missing second.
No system was wrong.
But floating-point rounding errors, combined with implicit truncation, created the bug.
Why This Happens
Computers store floating-point numbers in binary using the IEEE 754 standard.
Some decimal fractions (like 0.5) can be represented exactly.
Others (like 0.1 or 0.41666666667) cannot.
So small rounding errors are introduced.
Most of the time, those errors are harmless.
But when you're converting:
Decimal → Seconds
Seconds → Time
and relying on exact integer boundaries, even a tiny precision loss can shift your value by one second.
And in time systems, one second matters.
The Fix
To fix the one-second error, I chose a very simple approach.
I added one second to the converted time and then reset the seconds to 00.
Here’s the code:
time = Time.at(value.to_f).utc
time += 1
time = time.change(sec: 0)
How It Works
If the incorrect time becomes:
09:59:59
Adding one second changes it to:
10:00:00
Then setting the seconds to 00 keeps it at:
10:00:00
If the time is already correct, for example:
10:00:00
Adding one second makes it:
10:00:01
Then resetting the seconds to 00 brings it back to:
10:00:00
So in both cases, the final result is correct.
Why I Chose This Approach
We only care about hours and minutes in our system.
Seconds are not important for our use case.
