Excel Formula to Change the Date After 6 PM in WPS Office
Question details
User needs an Excel-compatible formula that returns today's date before 6:00 PM and tomorrow's date after 6:00 PM.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is building a date/time-dependent spreadsheet and needs the displayed date to roll forward after 6:00 PM.
- Observed behavior
- The desired formula should evaluate the current time or a stored date/time value and return today's or tomorrow's date based on the 6:00 PM cutoff.
Make sure the cell containing the date/time value is recognized as a valid Excel date/time value, because the MOD function and time comparisons depend on numeric date serial values.
Use an IF Formula with NOW() to Return Today’s or Tomorrow’s Date
Use this formula when you want the result to update automatically based on the current system time.
The formula compares the current time produced by NOW() against 6:00 PM. Before 6:00 PM it returns TODAY(); at or after 6:00 PM it returns TODAY()+1.
Click the cell where you want the result, type =IF(--TEXT(NOW(),"hh:mm")>--"18:00",TODAY()+1,TODAY()) and press Enter.
Adjust a Date and Time Stored in a Cell After 6 PM
Use this formula when the date and time are already stored in a specific cell such as A2.
Create the 6 PM Date Formula in WPS Spreadsheets
Open your workbook in WPS Spreadsheets and enter the same Excel-compatible formula to automatically switch from today’s to tomorrow’s date after 6:00 PM.
- 1. Open WPS Spreadsheets: Launch WPS Office and open the workbook where you want the date formula.
- 2. Select the result cell: Click the cell that should display today’s or tomorrow’s date based on the 6:00 PM cutoff.
- 3. Enter the formula: Type =IF(--TEXT(NOW(),"hh:mm")>--"18:00",TODAY()+1,TODAY()) in the formula bar and press Enter.
- 4. Verify the result: Check the cell before 6:00 PM and after 6:00 PM, or temporarily adjust your system time to confirm it updates.
Frequently Asked Questions
Does the formula update automatically when the system time passes 6:00 PM?
Yes. Because it uses the volatile functions NOW() and TODAY(), it recalculates when the workbook recalculates. Press F9 if the result does not refresh automatically.
Why does the formula return today's date at exactly 6:00 PM?
The formula uses a greater-than comparison against "18:00". At exactly 6:00 PM the condition is false, so it returns TODAY(). Change the operator to >= if you want tomorrow's date at 6:00 PM.
Can I apply the formula to a date and time stored in another cell?
Yes. Use =A2+IF(MOD(A2,1)<0.75,0,1) and replace A2 with the cell reference that contains your date/time value.
How do I format the result as a date in WPS Spreadsheets?
Select the cell, press Ctrl+1 to open Format Cells, choose Date, and then pick the desired date format.
