How to Fix Excel Autofill Skipping Seconds in Date and Time Values
Question details
The user needs to prevent Excel from skipping seconds when autofilling combined date and time increments.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Graphing data logger output over several days where the X-axis requires precise, combined date and time increments at one-second intervals.
- Observed behavior
- When autofilling down rows by one second, Excel skips certain seconds (e.g., jumping from 51 to 53) due to accumulated rounding errors.
Ensure your cells are formatted with a custom number format (such as yyyy-mm-dd hh:mm:ss) so you can accurately view the exact seconds being generated.
Use a Row-Based Formula to Calculate Exact Time
Prevent rounding errors by mathematically adding the exact fraction of a day to your absolute starting time.
Excel stores dates and times as serial numbers with a 15-digit precision limit. One second is exactly 1/24/60/60 (or 0.0000115740740740741) of a day. Autofilling manually rounded time values causes small discrepancies to accumulate, eventually skipping a second. Using an absolute formula eliminates this cumulative error.
Type your exact starting date and time in a specific cell, for example, cell A2.
In the cell directly below (A3), enter the formula =$A$2+ROW(A1)/24/60/60.
Press Enter, click on cell A3, and drag the fill handle downward to populate the subsequent rows with perfect one-second increments.
Add the Exact Decimal Value for One Second
Manually add the highest-precision decimal equivalent of one second to the previous cell.
Separate Date and Time into Different Columns
Reduce the precision load on the serial number by keeping the date and time values isolated in different cells.
Calculate Date and Time Flawlessly with WPS Spreadsheet
WPS Spreadsheet offers powerful formula calculation, precise cell formatting, and an intuitive autofill handle to easily manage massive sets of time-series data without friction.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your data logger output.
- 2. Apply custom formatting: Select your target column, press Ctrl+1, navigate to the Custom tab, and type yyyy-mm-dd hh:mm:ss.
- 3. Input the base time: Type your starting date and time in the first cell, such as A2.
- 4. Enter the precision formula: In cell A3, enter the formula =$A$2+ROW(A1)/24/60/60 to calculate the exact time increment.
- 5. Use the autofill handle: Click the small square at the bottom right of cell A3 and drag it downwards to instantly populate your accurate timeline.
Frequently Asked Questions
Why does Excel change time formats when autofilling?
Excel attempts to guess the pattern of the cells you are dragging. If you haven't applied a strict custom format, it might default to a standard time or date format. Always set a custom format like yyyy-mm-dd hh:mm:ss to force Excel to display the exact output.
How is one second represented in Excel serial numbers?
Excel calculates time as a fraction of a 24-hour day. Therefore, one second is equal to 1 divided by 24, divided by 60, divided by 60, which roughly equals 0.0000115740740740741.
Can I autofill milliseconds accurately in Excel?
Yes, you can autofill milliseconds by formatting your cells as hh:mm:ss.000. However, instead of simply dragging, you should add the exact decimal equivalent of your millisecond increment (e.g., 1/86400000 for 1 millisecond) using an absolute formula to avoid severe precision loss.
What is the maximum precision limit for numbers in Excel?
Excel has a fixed calculation precision limit of 15 significant digits. Any numbers extending beyond this are automatically rounded or truncated, which is why accumulating small fractions like seconds over large date values can cause calculation skips.
