Change Excel Time from 12 AM to 4 PM Without Changing Seconds
Question details
The user wants to shift existing time values from 12:00 AM to 4:00 PM while keeping the original minutes and seconds intact.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a range of time values recorded from midnight at short intervals, likely including seconds or half-second increments, and needs them converted to the corresponding PM time.
- Observed behavior
- Time values currently display around 12:00 AM; the user needs a way to add 16 hours without manually editing each second value.
Make sure the affected cells contain real time values rather than text, and decide whether to update the original range in place or use a helper column for formula results.
Update Existing Time Cells with Paste Special Add
Use this method when you want to change the original time values in place without creating extra formula columns.
Adding 16:00:00 to a time value increases it by 16 hours. Because Excel and WPS Spreadsheets store times as fractions of a 24-hour day, the Paste Special Add operation shifts 12:00:00 AM values to 4:00:00 PM while preserving the existing minutes and seconds.
Select an unused cell, type 16:00:00, and press Enter.
Select the helper cell, right-click it, and choose Copy, or press Ctrl+C.
Select the range containing the original time values, right-click the selection, choose Paste Special, select Add, and click OK. In WPS Spreadsheets, you can also go to Home > Paste > Paste Special.
Select the helper cell and press Delete to clear the 16:00:00 value.
Generate Corrected Times with the TIME Function
Use this formula method when you want to keep the original values and display adjusted times in a separate column.
Shift Excel Time from 12 AM to 4 PM in WPS Spreadsheets
WPS Spreadsheets can adjust time values from midnight to 4 PM using Paste Special Add or the TIME function, keeping the existing minutes and seconds without manual retyping.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office, open the workbook that contains the time values, and select the worksheet with the midnight times.
- 2. Add 16 hours with Paste Special: Enter 16:00:00 in an empty cell, copy it, select the original time range, right-click, choose Paste Special > Add, and click OK.
- 3. Verify the converted times: Confirm that 12:00:xx AM values now display as 4:00:xx PM and that the seconds have not changed.
- 4. Save the workbook: Press Ctrl+S or click Menu > Save to keep the updated time values in WPS Spreadsheets or Excel format.
Frequently Asked Questions
Can I update the original time cells without using a formula?
Yes. Enter 16:00:00 in an empty cell, copy it, select the time cells, then open Paste Special, choose Add, and click OK. This shifts the original values to 4:00 PM in place.
What should I do if my time values include half-second precision?
Use Copy and Paste Special > Add with 16:00:00. This preserves fractional seconds. The TIME function with SECOND can return whole seconds and may drop the half-second portion.
Does this method work when the times are stored as text?
No. Text entries cannot be added as time values. Convert them to real time values first by using the Text to Columns feature or by correcting cell formatting, then apply Paste Special Add.
How do I apply the formula to an entire column of times?
Enter =TIME(16,MINUTE(A2),SECOND(A2)) in the first output cell, then drag the fill handle down to match all original time rows.
Will adding 16 hours change the date in my time cells?
For values starting at midnight, adding 16:00:00 keeps the same date and moves the time to 4 PM. If your cells include a date component, the date remains the same because the result stays within the same day.
