How I Track a 14-Day Google Play Closed Test in a Spreadsheet

Play Console shows your progress toward the testing requirement. It doesn't tell you who joined when, who quietly left, or the first day you can apply for production access. For Google Play closed testing those dates are the whole game, so I keep them myself in a plain spreadsheet. This post is the sheet I use: the columns, formulas that work in Google Sheets, Excel and LibreOffice, a short Python version if you'd rather keep a CSV in your repo, and the five-minute check I run in Play Console every morning. What the rule needs from your data Personal developer accounts created after November 13, 2023 need a closed test with at least 12 testers opted in continuously for the 14 days before they apply. Google's Play Console Help page adds two details that matter for tracking: A tester who opts out before 14 days doesn't count. If they opt back in, the 14 days have to be consecutive again. So each tester boils down to two dates: when they opted in, and when (if ever) they opted out. Everything else in the sheet is context that helps you later. The sheet: one row per opt-in One row per opt-in. The summary cells on the right do the 12 testers for 14 days math for you. Col Header What goes in it A Tester A name or alias B Google account The exact account their Play Store uses C Opted in Date they finished the opt-in and installed from Play D Opted out Date they left. Blank while they're still in E Device Model and Android version F Last active Last date you saw them use the app or heard from them G Notes Feedback, bugs, anything for the production access form H Days in Formula I Status Formula J Active since Helper formula One rule keeps it honest: when someone rejoins, don't edit their old row. Add a new row with the new opt-in date. Their first stretch is dead anyway, and keeping it gives you a clean history if anyone asks. The formulas Data starts in row 2. Put these in row 2 and fill them down. Days in (column H): =IF(C2="","",IF(D2="",TODAY(),D2)-C2) Status (column I) shows "counts", "out", or how far along the tester is: =IF(C2="","",IF(D2"","out",IF(TODAY()-C2>=14,"counts","day "&(TODAY()-C2)))) Active since (column J) holds the opt-in date only for people still opted in, and is blank for everyone else: =IF(OR(C2="",D2""),"",C2) Then three summary cells, anywhere you like. Format the last one as a date. Opted in now: =COUNT(J2:J100) Opted in 14+ days: =COUNTIF(J2:J100,"