Shift Excel Timestamps by Six Hours While Keeping Related Data
Question details
Shift timestamps recorded at 10-minute intervals from 6:00 AM–6:00 PM to a 12:00 AM–12:00 PM schedule while preserving the related data in every record.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The timestamp column needs to be moved six hours earlier without separating or altering the data associated with each timestamp row.
- Observed behavior
- The existing times begin at 6:00 AM and end at 6:00 PM, but the required schedule begins at 12:00 AM and ends at 12:00 PM.
Make sure the timestamp cells are real Excel time or date-time values, not text. Because the related data must stay matched to each record, change only the timestamp column and do not sort that column by itself.
Subtract Six Hours in a Temporary Timestamp Column
Use this method to shift existing time or date-time values six hours earlier while keeping all related row data in place.
Excel stores time as a fraction of one day. Six hours equals 0.25, so subtracting 0.25 changes 6:00 AM to 12:00 AM and 6:00 PM to 12:00 PM.
Right-click the column next to the timestamp column, such as column B beside timestamps in column A, and select Insert.
In the first temporary cell, such as B2, enter =A2-TIME(6,0,0). You can also use =A2-0.25.
Select B2 and drag the fill handle down, or double-click the fill handle, to apply the formula to all timestamp records.
Copy the adjusted cells in column B, select the first original timestamp cell in column A, then use Home > Paste > Paste Values to replace the original times without moving any related data.
Verify that the first and last times are 12:00 AM and 12:00 PM, then right-click the temporary column header and select Delete.
Use a Date-Time Formula When Timestamps Include Dates
Use this option when each timestamp contains both a date and a time and you need to retain the original dates where possible.
Build Additional Time Records with Power Query
Use Power Query when you need to create additional schedule rows or merge generated timestamps with related data instead of simply shifting existing records.
Shift Timestamp Schedules Easily in WPS Spreadsheets
WPS Spreadsheets can adjust time and date-time values with the same familiar formulas used in Excel. Subtract six hours in a helper column, paste the calculated values over the original timestamp column, and keep each row's related data together.
- 1. Open the workbook in WPS Spreadsheets: Open the Excel workbook in WPS Office and select the worksheet containing the timestamp column.
- 2. Add a helper column: Insert an empty column beside the timestamps by right-clicking a column header and selecting Insert.
- 3. Calculate the earlier times: In the first helper cell, enter =A2-TIME(6,0,0), adjusting A2 to match the first timestamp cell.
- 4. Fill and paste values: Fill the formula down, copy the helper results, then use Paste Special > Values to replace the original timestamp values.
- 5. Delete the helper column: Check the adjusted time range, then delete the temporary helper column when the results are correct.
Frequently Asked Questions
What formula changes 6:00 AM to 12:00 AM in Excel?
If the original time is in A2, enter =A2-TIME(6,0,0) in another cell. This subtracts six hours, so 6:00 AM becomes 12:00 AM.
Why should I use a temporary column to shift timestamps?
A temporary column lets you review the adjusted times before replacing the originals. It also leaves every row intact, so related data remains associated with the correct timestamp.
Can I use 0.25 instead of TIME(6,0,0)?
Yes. Excel treats one day as 1, and six hours as 0.25. Use =A2-0.25 to shift a value six hours earlier, or =A2+0.25 to shift it six hours later.
Will subtracting six hours change the date in a date-time cell?
Yes, when the result crosses midnight. For example, subtracting six hours from 3:00 AM on May 10 returns 9:00 PM on May 9. Format the result as a date and time to verify it.
