'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Return a Value Based on a 30-Minute Time Window in Spreadsheets
Question details
The user needs a formula to compare a check-in date and time against an expected delivery date and time, and return a specific load number if they fall within 30 minutes of each other.
- Product
- WPS Spreadsheet / Microsoft Excel
- Device & OS
- not provided
- Scenario
- Tracking logistics by comparing actual check-in times with scheduled delivery times to dynamically pull valid load numbers from a dataset.
- Observed behavior
- The formula needs to output the load number from column B when the dates match and the time difference is less than or equal to 30 minutes, otherwise it should return a blank cell.
Ensure that your date and time columns are properly formatted as numeric dates and times (not text strings) so that mathematical time operations function correctly.
Use a Combined IF and ABS Formula to Compare the Time Window
Calculate the exact time difference using a combination of logical and mathematical functions to evaluate if the check-in time falls within 30 minutes of the expected delivery time.
To check if two times are within a specific window, you can subtract one from the other. By using the ABS (Absolute) function, you can account for check-ins that are either 30 minutes early or 30 minutes late.
The INT function is used to extract the date portion from a combined datetime cell, while the MOD function extracts just the time.
Locate the check-in date (e.g., AH2), check-in time (e.g., AI2), expected delivery date and time (e.g., AK2), and the load number (e.g., B2).
Use the INT(AK2) function to extract just the date from the expected delivery date/time cell to compare it with the check-in date in AH2.
Use MOD(AK2, 1) to get the time from the expected delivery cell. Then, calculate the absolute difference using ABS(AI2 - MOD(AK2, 1)) <= TIME(0,30,0) to ensure the difference is exactly 30 minutes or less.
Select the cell where you want the load number to appear and enter the full formula: =IF(AND(AH2=INT(AK2),ABS(AI2-MOD(AK2,1))<=TIME(0,30,0)),B2,""). Press Enter.
Click the small square at the bottom-right corner of the cell containing your new formula and drag it down to apply the logic to all rows in your logistics dataset.
Easily Manage Logistics and Time Calculations with WPS Spreadsheet
WPS Spreadsheet provides powerful data processing capabilities, including advanced date, time, and logical functions, to help you track logistics efficiently and accurately.
- 1. Download and install: Get WPS Office from the official website and open your logistics spreadsheet.
- 2. Enter your data: Ensure your date, time, and load number columns are clearly defined and appropriately organized.
- 3. Format cells: Select the time and date cells, right-click and choose 'Format Cells' to apply the correct Date or Time formatting.
- 4. Apply the time window formula: Paste the =IF(AND(...)) formula into your target column to automatically filter and extract load numbers.
Frequently Asked Questions
Why does my formula return an error or unexpected text?
This usually happens if your date or time cells are formatted as text instead of numerical dates and times. Select the cells, go to 'Format Cells', and apply the correct Date and Time formats.
How can I change the time window from 30 minutes to 1 hour?
Simply modify the TIME(0,30,0) portion of the formula to TIME(1,0,0). The TIME function syntax is TIME(hours, minutes, seconds).
What if my check-in date and time are merged into the same cell?
If your check-in date and time are combined in a single cell (e.g., AH2), you can simplify the formula to compare the two datetime values directly using =IF(ABS(AH2-AK2)<=TIME(0,30,0),B2,"").
Does the ABS function matter if the check-in is always late?
The ABS function guarantees the difference is mathematically positive. While you could drop it if check-ins are strictly late (post-delivery time), using ABS creates a robust bidirectional time window that accounts for both early and late arrivals.
