Calculate Open Tickets at Each Month-End in Excel
Question details
Calculate how many tickets were still open at the end of each month, including tickets that remained open across multiple years.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Create a monthly ticket-backlog report using ticket creation dates and closure dates.
- Observed behavior
- A formula based only on the current Open status cannot accurately reconstruct historical month-end totals because tickets closed later must still count in earlier months.
For the examples below, place current status in column B, creation dates in column C, and closure dates in column D, with tickets in rows 2–16. Use real spreadsheet dates, leave closure dates blank for unclosed tickets, and enter a date from the first reporting month in G2.
Count historical month-end open tickets using creation and closure dates
Use this method for an accurate historical backlog, including tickets that are closed now but were open in earlier months.
A ticket is open at month-end if it was created before the next month begins and either has no closure date or closes on or after the next month's first day. Using the next-month boundary also handles dates that include times.
Enter the first reporting month as a date in G2, such as 1/1/2017. In G3, enter =EDATE(G2,1), then drag the fill handle down through the final reporting month. Format column G as mmm yyyy if desired.
In H2, enter =COUNTIFS($C$2:$C$16,">0",$C$2:$C$16,"<"&(EOMONTH(G2,0)+1),$D$2:$D$16,"")+COUNTIFS($C$2:$C$16,">0",$C$2:$C$16,"<"&(EOMONTH(G2,0)+1),$D$2:$D$16,">="&(EOMONTH(G2,0)+1)). The first COUNTIFS counts unclosed tickets; the second counts tickets closed after the reporting month. The >0 condition excludes missing creation dates.
Drag H2's fill handle down alongside the monthly dates. A ticket created in January 2017 and closed in February 2020 should count from January 2017 through January 2020, but not at the end of February 2020.
Count currently open tickets created by each month-end
Use this simpler formula only when you want to group today's open tickets by their creation cutoff, not reconstruct past backlog.
Calculate Monthly Ticket Backlogs in WPS Spreadsheet
Use WPS Spreadsheet to build the same month-end report with COUNTIFS, EDATE, and EOMONTH. Compare creation and closure dates to count tickets that were open at each reporting cutoff.
- 1. Open the ticket workbook: Open WPS Spreadsheet and use Menu > Open to select your Excel workbook. For this example, use column C for creation dates and column D for closure dates, leaving unclosed tickets' closure cells blank.
- 2. Create the reporting months: Enter the first reporting-month date in G2. Enter =EDATE(G2,1) in G3 and drag the fill handle down to generate the remaining months.
- 3. Calculate the historical backlog: In H2, enter =COUNTIFS($C$2:$C$16,">0",$C$2:$C$16,"<"&(EOMONTH(G2,0)+1),$D$2:$D$16,"")+COUNTIFS($C$2:$C$16,">0",$C$2:$C$16,"<"&(EOMONTH(G2,0)+1),$D$2:$D$16,">="&(EOMONTH(G2,0)+1)). Adjust row 16 to match your dataset.
- 4. Complete and save the report: Fill H2 down alongside the month list, verify a ticket with a known closure date, and use Save As to save the report as an Excel .xlsx workbook.
Frequently Asked Questions
Why does filtering for Open give incorrect totals for earlier months?
Column B represents the current status, not the status at a past month-end. A ticket closed in February 2020 may have been open throughout 2019, but a current-status COUNTIFS formula excludes it from every month. Use creation dates in column C and closure dates in column D for historical reporting.
What do EDATE and EOMONTH do in this report?
In G3, =EDATE(G2,1) advances the date in G2 by one month. EOMONTH(G2,0) returns the last date of G2's month; adding 1 gives the next month's first day, which serves as the cutoff for the historical counting formula.
Does a ticket closed on the last day of the month count as open?
No. In this end-of-month report, a closure date or timestamp before the next month starts means the ticket is already closed at the reporting cutoff. A ticket closed on the first day of the next month still counts as open at the preceding month-end.
How do I extend the report across several years?
Enter =EDATE(G2,1) in G3 and fill down for as many months as needed, then fill the H2 counting formula down beside those dates. EDATE automatically moves from December to January of the next year.
What if tickets are reopened after being closed?
One creation date and one closure date cannot describe multiple open periods. Record each status change with its effective date or maintain separate open-and-close intervals, then calculate whether each ticket was open at each month-end cutoff.
