How to measure your wake-time variability from Apple Health in a spreadsheet

To calculate how consistent your wake-up time is from Apple Health, export your data, take one wake time per night, convert it to minutes after midnight, and run a standard deviation over at least 28 nights. A smaller number means a steadier wake time. Why bother: regularity has become a research variable of its own, and this is the cheap way to get your own figure.

Menu labels follow Apple’s Health user guide and may differ on your iOS version; the spreadsheet maths does not depend on Apple.

How do you get the data out of Apple Health?

1. Export. In the Health app, tap your profile picture, then Export All Health Data. It builds an export.zip that contains export.xml. Big histories take a few minutes.
2. Filter to sleep. Open export.xml in Excel (Data > Get Data > From File > From XML). In the Record table, filter type to HKCategoryTypeIdentifierSleepAnalysis. Each row has a startDate, an endDate and a value.
3. Pick one source. Since iOS 16, Apple’s sleep values include Core, Deep, REM, Unspecified, Awake and In Bed, and an iPhone and a Watch can both write records for the same night. Keep one sourceName, drop the In Bed and Awake rows, and you avoid double counting. Scan your own file for the exact value strings, since they differ by iOS version.

How do you turn records into one wake time per night?

4. Last end time wins. Group by calendar date and take the latest endDate among asleep records that end before about 2 p.m. Excluding afternoon records keeps naps out; night workers should put the cutoff in the middle of their waking hours instead. A pivot table with Max of end time does it.
5. Minutes after midnight. In a new column: =HOUR(x)*60+MINUTE(x). A 6:45 wake becomes 405.
6. Handle the wrap. Day workers can skip this, because a 6:45 wake never crosses midnight. If you work nights and wake near 12:00 a.m., add 1440 to every value under a cutoff you pick in your quietest hour, so 11:50 p.m. and 12:10 a.m. sit 20 minutes apart instead of 1,420.
7. Run it. =STDEV.S(range) in Excel, =STDEV(range) in Sheets. Use 28 nights or more, since two bad weekends swamp a short window.

What does the number mean?

It means your wake time typically lands within that many minutes of its average. A result of 25 says most mornings fall within about 25 minutes either side; weekend sleep-ins push it up fast. There is no clinical cutoff for wake-time standard deviation, and I would distrust any table that claims one.

The research uses a different measure. Phillips et al. (2017, Scientific Reports) introduced the Sleep Regularity Index and followed 61 Harvard undergraduates for 30 days. Irregular sleepers had a later melatonin onset (00:08 versus 21:32), and the index correlated with academic performance at r = 0.37. Windred et al. (2024, Sleep) scored 60,977 UK Biobank participants from wrist accelerometers, median index 81.0, with 1,859 deaths over a follow-up of up to 7.8 years (mean 6.3). Compared with the least regular fifth, the other four fifths had 20% to 48% lower all-cause mortality risk, and regularity predicted mortality better than sleep duration. Both are observational. Neither tested a wake-time SD, so use yours as a personal gauge. The regularity versus duration post covers how the index itself is computed.

If your number is higher than you’d like, don’t swing your alarm by an hour. The circadian clock moves slowly, which is why 15-minute steps are the usual advice. Rerun the sheet in a month.

Frequently asked questions

Can I do this in Google Sheets instead of Excel?

Mostly. Sheets has no built-in import for a local XML file, so the filtering step is the awkward part. Convert export.xml to CSV first (a short script or an online converter works), upload the CSV, then use STDEV and the same minute formula. Note that export.xml for a long history can run to hundreds of megabytes, which Sheets may refuse.

Why use standard deviation when the studies use the Sleep Regularity Index?

The Sleep Regularity Index compares your asleep or awake state at the same clock times 24 hours apart, so it reflects bedtime, wake time and awakenings together. Standard deviation of wake time captures only one edge of your sleep. It is easier to compute by hand, and the two will usually move together, but no published conversion between them exists.

How big was the mortality difference in the Windred study?

Against the least regular fifth of participants, fully adjusted hazard ratios for all-cause mortality were 0.80, 0.75, 0.72 and 0.70 across the four more regular fifths (Table 2). Follow-up averaged 6.3 years, with a maximum of 7.8.