Calculate Elapsed Hours When a Date Is Blank in Microsoft Lists
Question details
The user needs a formula to calculate the elapsed hours between a received date and a closed date, while dynamically using the current time if the ticket remains open (the closed date is blank).
- Product
- Microsoft Lists / Spreadsheets
- Device & OS
- not provided
- Scenario
- Calculating the duration or open hours for tracking tickets and projects when some items do not yet have a recorded closed date.
- Observed behavior
- Standard date subtraction fails when the end date is missing. Furthermore, utilizing the TODAY() function results in negative hour values for tickets created on the same day because it calculates from midnight.
Ensure your list or spreadsheet date columns are formatted to include both date and time values for the most accurate hour calculations.
Use IF and NOW Functions to Calculate Elapsed Hours
Combine an IF statement with the NOW() function to calculate the time difference using the current time whenever the closed date column is empty.
This formula checks if the 'Date ticket closed' column is blank. If true, it subtracts the 'Date ticket was received' from the current system time using the NOW() function. If a closed date exists, it calculates the difference between the two dates.
Multiplying the result by 24 converts the difference from days into hours, and the INT() function rounds it down to the nearest whole hour.
Navigate to your list or spreadsheet settings and add a new Calculated Column or select an empty cell.
Type the exact formula: =IF([Date ticket closed]="",INT((NOW()-[Date ticket was received])*24),INT(([Date ticket closed]-[Date ticket was received])*24))
Set the output data type to a Number (with zero decimal places if preferred) and click Save or press Enter.
Calculate Elapsed Time Effortlessly in WPS Spreadsheet
WPS Spreadsheet seamlessly handles complex date and time calculations using standard IF, NOW, and INT functions. Manage tracking lists with high precision and full formula compatibility.
- 1. Set up your date columns: Open WPS Spreadsheet and create a 'Received Date' column (e.g., A2) and a 'Closed Date' column (e.g., B2).
- 2. Enter the formula: In an adjacent cell (e.g., C2), enter the formula: =IF(B2="", INT((NOW()-A2)*24), INT((B2-A2)*24)).
- 3. Apply to all rows: Press Enter, then click and drag the fill handle at the bottom-right corner of the cell to apply the formula to the rest of your list.
Frequently Asked Questions
Why does my formula return negative hours when calculating same-day tickets?
If you use the TODAY() function, it calculates time from 12:00 AM (midnight) of the current day. To accurately factor in the current hour and avoid negative results, replace TODAY() with NOW() in your formula.
Does the NOW() function update dynamically in real time?
No, list-based systems do not continually refresh formulas. The NOW() function only updates when the specific record is created, modified, or when you manually force a column recalculation.
Can I calculate the elapsed time in minutes instead of hours?
Yes. Instead of multiplying the date difference by 24 (the number of hours in a day), multiply the result by 1440 (the number of minutes in a day) to output the total elapsed minutes.
