How to Add Hours, Minutes, and Seconds Without Losing the Date in Excel
Question details
The user needs to add separate hours, minutes, and seconds values to the current date and time in Excel and have excess hours carry into the next date instead of resetting.
- Product
- WPS Office Excel
- Device & OS
- not provided
- Scenario
- The user is working with date and time data in an Excel worksheet and wants to add hours, minutes, and seconds from separate cells without losing the calculated date.
- Observed behavior
- Using the TIME function with values greater than 24 hours wraps the time back to the beginning of the day, so the date does not advance correctly.
Make sure the hours, minutes, and seconds are stored in separate cells and that the result cell is not formatted as Text. Confirm the workbook date system is consistent if you are combining dates from different sources.
Add Time Units Directly with Fractional Day Arithmetic
Use this method when hours can exceed 24, because it converts each time unit to a day fraction and automatically carries excess time into the next date.
Excel and WPS Spreadsheets store dates as whole-day serial numbers and times as fractional parts of a day. Dividing hours by 24, minutes by 1440, and seconds by 86400 adds those fractions to the current serial date, so the date advances when the total passes midnight.
Click the cell where the combined date and time should appear, such as E3.
Type =NOW()+B3/24+C3/(24*60)+D3/(24*60*60), then press Enter. Replace B3, C3, and D3 with the actual cells that contain hours, minutes, and seconds.
Right-click the result cell, choose Format Cells, and select Date or Custom. Use a format such as m/d/yyyy h:mm:ss to show the date and time together.
Use the TIME Function Only for Single-Day Time Offsets
Use this shorter formula only when the total time offset is less than 24 hours; it is simpler but can wrap if hours exceed 23.
Add Hours, Minutes, and Seconds Without Losing the Date in WPS Spreadsheets
WPS Spreadsheets supports the same Excel date/time formulas and .xlsx workbooks, so you can add hours, minutes, and seconds directly to NOW() and keep the correct date when the result crosses midnight.
- 1. Download and install WPS Office: Go to the official WPS website, download WPS Office, and install WPS Spreadsheets on your device.
- 2. Open the workbook: Open the Excel workbook that contains the hours, minutes, and seconds cells.
- 3. Enter the formula: In the result cell, type =NOW()+B3/24+C3/(24*60)+D3/(24*60*60) and press Enter.
- 4. Apply date/time formatting: Right-click the cell, select Format Cells, and choose a Date or Custom format that includes both date and time.
- 5. Save the file: Click Menu, choose Save As, and select .xlsx to keep the file compatible with Microsoft Excel.
Frequently Asked Questions
Why does my date reset when I use the TIME function for more than 24 hours?
The TIME function returns a time of day, so an hour value of 24 or more wraps back within the same day. Use =NOW()+B3/24+C3/1440+D3/86400 instead to carry excess hours into the next date.
How do I format the result to show both the date and time?
Right-click the result cell, choose Format Cells, select Date or Custom, and enter a format such as m/d/yyyy h:mm:ss.
Can I add hours, minutes, and seconds to a fixed date instead of the current date?
Yes. Replace NOW() with the cell containing the start date, for example =A3+B3/24+C3/1440+D3/86400, and format the result as a date and time.
Does this formula work in both WPS Spreadsheets and Microsoft Excel?
Yes. WPS Spreadsheets uses the same workbook format and date/time arithmetic, so the formula behaves the same in both applications.
