WPS Spreadsheet: Move Excel Dates to the Next Workday After 3 PM
Question details
The user needs to move a received timestamp to 8:00 AM on the next working day when it is received after 3:00 PM.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A received date and time is stored in cell A2, and timestamps after 3:00 PM must be rescheduled for the next weekday at 8:00 AM.
- Observed behavior
- The required result is a date and time that skips Saturday and Sunday and preserves the original timestamp when it is not after 3:00 PM.
Confirm that A2 contains a valid date-and-time value rather than text, and identify the cell where the adjusted timestamp should appear. Ensure the output cell is formatted to display both the date and time.
Use WORKDAY to move timestamps after 3 PM to 8 AM
Use this formula when only timestamps later than 3:00 PM should move to the next weekday.
WPS Spreadsheet stores the date as the integer portion and the time as the fractional portion. MOD(A2,1) extracts the time, while WORKDAY skips Saturday and Sunday when calculating the next working day.
Select the output cell and enter =IF(MOD(A2,1)>TIME(15,0,0),WORKDAY(INT(A2),1)+TIME(8,0,0),A2), replacing A2 if your received timestamp is in another cell.
Press Enter, then drag the fill handle down to apply the formula to the remaining received timestamps.
Select the result cells, open the Home tab, choose the Number Format menu, and apply a date-and-time format so the 8:00 AM time is visible.
Adjust Workday Timestamps Easily in WPS Spreadsheet
WPS Spreadsheet can evaluate the WORKDAY, INT, MOD, and TIME functions needed to reschedule late timestamps, skip weekends, and display the adjusted result in a familiar spreadsheet interface. It also supports Microsoft Excel workbooks for easy migration.
- 1. Open the workbook: Launch WPS Office, choose Spreadsheet, and open the workbook containing the received timestamps.
- 2. Select an output cell: Click an empty cell beside the original timestamp column where the adjusted date and time should appear.
- 3. Enter the workday formula: Type =IF(MOD(A2,1)>TIME(15,0,0),WORKDAY(INT(A2),1)+TIME(8,0,0),A2) and press Enter.
- 4. Fill and format the results: Drag the fill handle down the column, then use Home > Number Format to display the results as dates and times.
Frequently Asked Questions
What formula moves a timestamp after 3 PM to the next workday at 8 AM?
In WPS Spreadsheet, enter =IF(MOD(A2,1)>TIME(15,0,0),WORKDAY(INT(A2),1)+TIME(8,0,0),A2), assuming the original timestamp is in A2.
Does the WORKDAY formula skip weekends automatically?
Yes. WORKDAY(INT(A2),1) advances to the next Monday when A2 falls on a Friday after 3 PM, and it skips Saturday and Sunday in other cases.
What happens when the timestamp is exactly 3:00 PM?
The formula leaves it unchanged because the condition uses >TIME(15,0,0). Use >=TIME(15,0,0) if exactly 3:00 PM should also move to the next workday.
How can I include company holidays in the calculation?
List holiday dates in a range, such as H2:H20, and use WORKDAY(INT(A2),1,H2:H20) so the next working day also excludes those holidays.
Why does the result show a number instead of a date and time?
The cell is probably using General formatting. Select the result cells, open Home > Number Format, and choose a date-and-time format.
