How to Move Dates After 6 PM to Next Business Day in Excel
Question details
The user needs a date and time formula to shift entries recorded after 6 PM or during weekends to 9 AM on the following business day, while keeping weekday entries before 6 PM unchanged.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking task submissions, tickets, or timestamps where out-of-hours requests must be logged as received at the start of the next working day.
- Observed behavior
- The user wants a calculated field that evaluates the original timestamp, identifies if it falls outside business hours, and outputs the adjusted business hours timestamp.
Ensure your source data contains valid date and time values rather than plain text. It is also helpful to verify your system's default date format so the formula processes the integer and fractional time parts accurately.
Use a Nested IF and WEEKDAY Formula
Use a comprehensive formula that checks the day of the week and the time of day to push after-hours entries to 9 AM the next business day.
This solution relies on the WEEKDAY, INT, and TIME functions. In Excel, dates are stored as integers and times as fractional values. By using INT(A2), you can extract just the date, while A2-INT(A2) isolates the time. The formula evaluates whether the day is a weekend or if the time is past 6 PM (18:00) and adds the necessary days to reach the next valid business day at 9 AM.
Enter your original date and time values into column A, starting at cell A2.
In cell B2, input the following formula: =IF(AND(WEEKDAY(A2,2)<6,A2-INT(A2)<TIME(18,0,0)),A2,IF(AND(WEEKDAY(A2,2)=4,A2-INT(A2)>=TIME(18,0,0)),INT(A2)+1+TIME(9,0,0),IF(AND(WEEKDAY(A2,2)=5,A2-INT(A2)>=TIME(18,0,0)),INT(A2)+3+TIME(9,0,0),IF(WEEKDAY(A2,2)=6,INT(A2)+2+TIME(9,0,0),IF(WEEKDAY(A2,2)=7,INT(A2)+1+TIME(9,0,0),INT(A2)+TIME(9,0,0))))))
Right-click cell B2, select 'Format Cells', navigate to the 'Number' tab, choose 'Custom', and enter a date-time format like m/d/yyyy h:mm AM/PM.
Drag the fill handle from the bottom-right corner of cell B2 down to apply this formula to the rest of your data set.
Create a Custom VBA Function (UDF)
If you frequently need to adjust timestamps for business hours across multiple workbooks, a custom User Defined Function in VBA provides a cleaner alternative to long nested formulas.
Easily Manage Business Hour Calculations in WPS Spreadsheet
WPS Spreadsheet fully supports complex nested IF, WEEKDAY, and TIME formulas, ensuring your business hour calculations work seamlessly. Enjoy a lightweight, highly compatible alternative to process your data effectively.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your workbook containing the timestamp data.
- 2. Input the Time Formula: Select the target cell and paste the nested IF and WEEKDAY formula to calculate the next business day.
- 3. Format Cells: Use the shortcut Ctrl + 1 to open the Format Cells dialog and apply a custom date-time format.
- 4. Drag to Fill: Double-click the fill handle at the bottom-right of the cell to apply the formula down the column instantly.
Frequently Asked Questions
Why is my formula returning a number like 44200.375 instead of a date?
Spreadsheet applications store dates and times as serial numbers, where the integer represents the date and the decimal represents the time. To display it correctly, right-click the cell, select 'Format Cells', choose 'Custom', and apply a format such as mm/dd/yyyy hh:mm AM/PM.
How do I account for public holidays in this formula?
The provided formula only evaluates standard weekends. To skip holidays, you would need to integrate the WORKDAY or WORKDAY.INTL function combined with a separate range containing your holiday dates. Because you are factoring in specific cutoff times (6 PM), combining this usually requires an even more complex nested structure or a VBA macro.
Can I change the cut-off time from 6 PM to 5 PM?
Yes. You can modify the formula by changing TIME(18,0,0) to TIME(17,0,0). The TIME function format is TIME(hour, minute, second), utilizing a 24-hour clock.
Does WPS Spreadsheet support the WEEKDAY and TIME functions?
Absolutely. WPS Spreadsheet is highly compatible with standard Excel functions, including WEEKDAY, TIME, INT, and nested IF statements, allowing you to use this exact formula without any modifications.
