How to Calculate the Number of Days a Ticket Has Been Open in Excel
Question details
Calculate the duration a ticket has been active using a known start date and dynamic end dates that may be blank.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking ticket or project durations where the end date is initially unknown but will be populated upon completion.
- Observed behavior
- Requires a formula that dynamically calculates days open based on the current date for active tickets, and locks the duration once a closing date is entered.
Ensure your start dates and end dates are formatted as valid 'Date' values in Excel so the subtraction formula calculates the days accurately.
Calculate Days Open for Active Tickets (Basic)
Use this simple formula if you only need to calculate the days elapsed from a start date to the current date.
If you do not need to account for closing dates or empty rows, you can perform a simple subtraction using the TODAY() function.
Click on the cell where you want the number of days to appear (e.g., C2).
Assuming your start date is in cell A2, type the formula =TODAY()-A2 and press Enter.
If the result displays as a date, go to the Home tab and change the Number Format from 'Date' to 'General' or 'Number'.
Use the IF Function for Dynamic Start and End Dates
This method automatically switches between the current date and the closing date, while leaving the cell blank if there is no start date.
Easily Manage Tracking Sheets with WPS Spreadsheet
WPS Office fully supports all standard date and logical functions, including TODAY, IF, and IFS, making it perfectly suited for tracking ticket durations, project timelines, and daily workflows.
- 1. Open your tracker: Launch WPS Spreadsheet and open your ticket or project tracking document.
- 2. Apply the formula: Select the duration cell and type your IF/TODAY formula to dynamically calculate the days.
- 3. Format and drag: Format the cell as a number and drag the fill handle to seamlessly apply the logic across all your active and closed tickets.
Frequently Asked Questions
Why does my formula result look like a weird date instead of a number?
When you subtract dates, the spreadsheet software sometimes automatically assumes the result should also be a date format. To fix this, select the cells with your formula, navigate to the Home tab, and change the Number Format dropdown from 'Date' to 'General' or 'Number'.
Can I exclude weekends from the days open calculation?
Yes. Instead of simple subtraction, use the NETWORKDAYS function to count only business days. For example, use =NETWORKDAYS(A2, IF(B2<>"", B2, TODAY())) to exclude Saturdays and Sundays from the duration.
What happens if the start date is in the future?
If a start date is logged in the future and there is no end date, the formula will return a negative number. You can prevent this by wrapping your calculation in a MAX function, such as =MAX(0, IF(B2<>"",B2,TODAY())-A2), which ensures the result never falls below zero.
