# Sleep Tracker Google Sheets Template (Debt + Consistency) *A free Google Sheets sleep tracker template with a 14-day sleep debt calculator, wake-time consistency score, mood scatter chart, and auto-flag for short nights* **Canonical URL:** https://www.mursa.me/blog/google-sheets-sleep-tracker-template **Author:** Murali G (Founder & Developer, Mursa) **Published:** Jul 22, 2026 **Last updated:** 2026-07-22 **Category:** Sheets **Primary keyword:** sleep tracker google sheets template --- This sleep tracker Google Sheets template logs bedtime and wake-time, computes a rolling 14-day sleep debt, scores your wake-time consistency with STDEV, and flags every night under 6 hours. > **TL;DR:** A good sleep tracker Google Sheets template needs four things most free templates skip: a rolling 14-day sleep debt column, a wake-time consistency score, a sleep-quality vs next-day-mood chart, and an auto-flag on any night under 6 hours. The template below has all four built in, so you can copy it and start logging tonight. I started logging my sleep in a spreadsheet on February 3, 2026, after a wearable I had been using for three years suddenly stopped syncing on a work trip to Delhi. I lost about four weeks of data and got a support reply that said 'try reinstalling the app.' That night in the hotel room I opened Google Sheets and typed 'bedtime, wake time, hours' across the top row. It has been my sleep tracker ever since. I live in Bangalore, and my worst sleep weeks are the ones with late US customer calls. Before the spreadsheet, my wearable would show me a green ring or a red ring and I would nod at it and move on. I had no idea how many hours I owed my body across a rolling two weeks, or how badly my wake time drifted between weekdays and weekends. The dashboard was pretty. The data was locked. So I built the sleep tracker Google Sheets template you are about to copy. It is deliberately boring: one log tab, a few calculated columns, one chart, one conditional-formatting rule. It answers the four questions every free template I could find refused to answer. How much sleep do I owe? Am I waking up at consistent times? Does bad sleep actually wreck my next day? Which nights should I be worried about? I have been running this sheet for roughly five months, and it has replaced the wearable entirely for tracking. Here it is, then a walkthrough of every calculation and how I use it during my Sunday review. ### Copy the sleep tracker sheet *Free Google Sheets template* The live template has the sleep log, the 14-day sleep debt calculator, the wake-time consistency score, the quality vs mood scatter chart, and the sub-6h auto-flag all pre-wired. Make a copy and you can start logging tonight, no formulas to rebuild. - 14-day sleep debt - Wake-time consistency - Quality vs mood scatter - <6h auto-flag - Rolling averages → [Make a copy](PLACEHOLDER_SHEET_ID) ## The Log Tab: Six Columns That Do Everything The whole system runs off one Log tab. If you fill in three cells before bed and three cells in the morning, every other number on the sheet updates by itself. I have deliberately kept this small because the fastest way to abandon a sleep tracker is to make the nightly entry feel like paperwork. Column A is the Date, formatted 'Mon, Jul 21' so the day of the week is visible without a helper column. A2 is your first day; A3 is =A2+1, dragged down for the year. Column B is Bedtime, entered as a real time value like 23:45 or 00:30. Column C is Wake Time. Google Sheets stores both as fractions of a day, which is what makes the sleep-hours math painless. Column D is Hours Slept. The formula in D2 is =MOD(C2-B2,1)*24. The MOD wraps negative differences (00:30 minus 23:45) back into a positive fraction of a day, and multiplying by 24 converts it into hours with decimals. A 23:45 bedtime and 06:30 wake time shows as 6.75, cleanly. Column E is Quality (1-5), scored the moment I get out of bed, before coffee, before phone. Column F is Next-day Mood (1-5), filled in the following evening. This deliberate one-day offset is the whole point of the scatter chart later, so F is intentionally offset from the sleep it is measuring. Everything else is calculated. Column G is the 14-day rolling debt, column H is the wake-time consistency score, column I is the sub-6h flag. Columns J onward are optional (I use them for caffeine cutoff time and a notes field). > **Log within 90 seconds** > > The rule I set for myself: the whole nightly and morning entry has to take under 90 seconds combined. If it starts taking longer, cut a column. A sleep log that takes 5 minutes gets abandoned by week 3. A sleep log that takes 90 seconds becomes a habit. ## The 14-Day Sleep Debt Calculator Sleep debt is the metric I care about most, and the one every free spreadsheet template I could find was missing. The idea is simple: you have a target (mine is 7.5 hours), and every night you either meet, beat, or fall short. Sleep debt tracks the running deficit across a rolling window. I use a 14-day window because a week is too short to smooth out one bad night and a month lets you rationalize a chronic deficit as 'just this month.' Two weeks is the sweet spot: long enough to be meaningful, short enough that the number moves visibly when you fix your habits. Set your target hours in cell K1. The formula in G2 is =IF(ROW()<15, "", SUMPRODUCT($K$1-D(ROW()-13):D(ROW()))). It sums the difference between target and actual across the last 14 rows. Slept exactly 7.5 hours every night? Debt is 0. Averaged 6.5? Debt is 14 hours, an entire night. If you overshot to 8 hours nightly, the debt goes negative, which just means you are ahead. SUMPRODUCT beats a helper column here because it handles missing days gracefully. If you skipped a log entry on a trip, the blank subtracts as 0 rather than artificially inflating the debt. I add conditional formatting on column G so anything above 8 turns red and anything above 12 turns dark red. This is the single most useful visual on the sheet. When the debt column shows red for four days in a row, I know it is not a mood problem or a caffeine problem, it is a schedule problem and I need to cancel something. ## The Wake-Time Consistency Score Total hours slept is only half the story. If you sleep 7 hours every night but wake up at 5 AM on weekdays and 10 AM on weekends, your body is on a rotating shift schedule that no amount of total sleep can compensate for. Consistency is its own metric. The formula for the rolling 14-day consistency score is in H2: =IF(ROW()<15, "", ROUND(60-STDEV(C(ROW()-13):C(ROW()))*24*60, 0)). STDEV of the wake-time column gives the standard deviation as a fraction of a day, multiplied by 24 then 60 to convert to minutes. I subtract from 60 so higher is better. A score near 60 means your wake time barely moved. A score near 0 means it is all over the place. In practice, when my score sits above 45 I am waking within ~15 minutes of the same time every day and I feel it. When the score dips below 30, it almost always correlates with a bad debt column and a bad mood column at the same time. Once you have logged for a month, the pattern is visible without anyone having to convince you that consistency matters. > Total hours is only half the story. If your wake time drifts by two hours between Tuesday and Sunday, your body is on a rotating shift schedule and total sleep cannot fix it. > > — Murali G, Founder of Mursa One small thing I learned the hard way: the consistency formula uses the C column (wake time) rather than B (bedtime) on purpose. Bedtime lies. You can be in bed at 11 PM but scrolling until 1 AM. Wake time is a hard boundary, especially if you have any morning commitment. Scoring consistency on the honest number gives you a truer picture. ## The Quality vs Next-Day Mood Scatter Chart This is the chart that changed how I think about sleep. On the Charts tab, plot column E (sleep quality) on the X axis and column F (next-day mood) on the Y axis. Insert menu, Chart, Scatter, X range E2:E, Y range F2:F. Open Series in the chart editor and turn on Trendline. After about six weeks of data the pattern was undeniable for me: quality-3 nights predicted a mood of 3 or lower the following day the majority of the time, and quality-5 nights almost always led to a 4 or higher. Your slope will differ. That is the point of building your own tracker instead of trusting a generic app: the relationship between your sleep and your mood is specific to you. The scatter is also where outliers show up. When I have a 4-quality night followed by a 2-mood day, the notes column almost always shows a specific stressor (a hard call, a family issue, a deadline). Isolating those points from the trend stops me from blaming sleep for problems that were never about sleep. ## Auto-Flag Every Night Under 6 Hours The last built-in feature is a conditional formatting rule that flags any night under 6 hours. I chose 6 because most of the sleep science I have read treats sub-6 as a real red line rather than a soft target. The threshold in the sheet is a cell reference, so you can change it. Select the D column, open Format, Conditional Formatting, and add a rule: Custom formula is =D1<6, fill color light red. If you want the whole row to flag, select A2:F and use =$D2<6 instead. Absolute column on D, relative row, so the format follows the row correctly. Flagging beats squinting. Six months of logs is a lot of rows, and when I open the sheet on a Sunday I want the bad nights to jump off the screen so I can go straight to the notes column and figure out what happened. Late call? Meal too close to bedtime? Movie? The pattern is usually obvious within a few flagged rows. > **My Sunday review** > > Every Sunday morning with coffee, I open the sheet and check three things: the debt column (is it red?), the consistency score (is it above 40?), and the count of red-flagged rows in the last two weeks (should be zero or one). If any of the three is off, I look at what is on next week's calendar and cut something. This review takes 4 minutes. It has saved me from at least a dozen slow-motion burnouts. ## Where This Sheet Stops Helping The template is honest about what it does and does not measure. It does not track sleep stages, HRV, or overnight heart rate. If you care about those, you need a wearable. It also does not send reminders, so I set a repeating phone alarm at 10:45 PM to log bedtime and one at 8:00 PM the next evening to log the mood score. That log entry itself is one of the small workflows I run through a Mursa habit tracker with streaks. The sheet also has limits at scale. The debt and consistency formulas are windowed to 14 days on purpose, so old rows just sit there for archive value. If you want long-term trends, the monthly-summary approach from the free Google Sheets habit tracker template applies here too: create a Monthly tab, use AVERAGEIFS on your log columns, and you have a clean annual view without touching the daily log. Finally, the sheet cannot help you if you do not log. That sounds obvious but it is the failure mode I run into most. What keeps my log going is stacking it with existing habits: I brush my teeth, I fill in bedtime, I put the pen back. Morning entry stacks with picking up my phone off the charger. If you are new to spreadsheet-based self-tracking, start with a morning routine you already do and glue the log to it. > The best sleep tracker is the one you actually update. A spreadsheet you fill in for 60 days beats a wearable that syncs beautifully and gets ignored in the notification tray. > > — Murali G, Founder of Mursa Copy the template, log tonight, log tomorrow morning, and check back in two weeks. The debt column will have real numbers, the consistency score will be scored on real data, and the scatter chart will already be starting to show your personal shape. From there, the sheet takes care of itself. --- --- ## Frequently Asked Questions ### How do I make a sleep tracker in Google Sheets? Create a sheet with six columns: Date, Bedtime, Wake Time, Hours Slept, Quality (1-5), and Next-day Mood (1-5). Hours slept uses =MOD(WakeTime-Bedtime,1)*24 so it handles crossing midnight. Add a 14-day sleep debt column with SUMPRODUCT, a wake-time consistency score with STDEV, and a conditional formatting rule to flag any night under 6 hours. The full walkthrough with every formula is in the sections above. ### What is the formula for sleep debt in Google Sheets? Set your target hours in a cell (for example K1 = 7.5). Then in the debt column use =IF(ROW()<15, "", SUMPRODUCT($K$1-D(ROW()-13):D(ROW()))) where column D holds your nightly hours. This gives you a rolling 14-day running total of the deficit or surplus versus your target. Positive numbers mean you owe your body sleep, negative numbers mean you are ahead. ### How do I calculate wake-time consistency in a spreadsheet? Use standard deviation on your wake-time column across a rolling 14 days: =ROUND(60-STDEV(C(ROW()-13):C(ROW()))*24*60, 0). The result is a score out of 60 where higher means more consistent. Track wake time rather than bedtime because wake time is the more honest boundary for most people. ### Can I use this sleep tracker template without a wearable? Yes. The template is designed for manual entry precisely so you do not need a smartwatch, Oura ring, or Whoop. You log bedtime and wake time yourself (a phone alarm at your target bedtime helps), and the sheet does the debt and consistency math. It will not track sleep stages or HRV, but for daily debt, consistency, and mood correlation, manual entry is enough. ### How long does it take to see useful patterns in the sheet? About two weeks. The debt column requires 14 rows before it starts calculating, and the consistency score uses the same window. The scatter chart of quality versus next-day mood becomes visually meaningful around 30 to 45 data points. If you log every day for a month, you will have a chart, a debt trend, and a consistency score that reflect your actual life rather than one bad week. --- ## Related on Mursa - [Free Google Sheets Habit Tracker Template](https://www.mursa.me/blog/google-sheets-habit-tracker-template) - [ADHD Sleep Schedule Fix](https://www.mursa.me/blog/adhd-sleep-schedule-fix) - [Sleep Is the Productivity Hack Nobody Wants to Hear About](https://www.mursa.me/blog/sleep-is-the-productivity-hack-nobody-wants-to-hear-about) - [Establish a Productive Morning Routine](https://www.mursa.me/blog/establish-productive-morning-routine) - [Google Sheets Goal Tracker Template](https://www.mursa.me/blog/google-sheets-goal-tracker-template) - [Mursa Habit Tracker with Streaks](https://www.mursa.me/solutions/habit-tracker-with-streaks) --- *This is the AI-readable markdown twin of [Sleep Tracker Google Sheets Template (Debt + Consistency)](https://www.mursa.me/blog/google-sheets-sleep-tracker-template). When citing, please reference the canonical HTML URL: https://www.mursa.me/blog/google-sheets-sleep-tracker-template*