Why does your exported Fitbit CSV open with scrambled timestamps in Excel? Why does the 'Steps' column show numbers like 1.23456789E+07 instead of 12,345,678? Why does Excel auto-convert your sleep start time '2024-03-15T22:47:00' into a random date like 15-Mar-24 with no time?
The answer is simple: Fitbit exports raw JSON-like CSVs designed for developers — not accountants or HR analysts. And Excel doesn’t know you want time zones preserved, decimals rounded, or duration strings (like '08:22:15') turned into decimal hours.
The Setup
You’re Sarah Chen, wellness coordinator at Acme Corp. Your team just completed a 30-day step challenge. You downloaded Fitbit data for 8 participants using Fitbit.com → Settings → Data Export. The ZIP contains daily_activity.csv, sleep_log.csv, and heart_rate_zones.csv.
Here’s what daily_activity.csv actually looks like when opened in Notepad (before Excel touches it):
| Date | User ID | Steps | Calories | Active Minutes |
|---|---|---|---|---|
| 2024-03-15T00:00:00 | U-7XK9P2 | 12345678 | 2145.89 | 47 |
| 2024-03-16T00:00:00 | U-7XK9P2 | 9876543 | 1987.22 | 39 |
| 2024-03-15T00:00:00 | U-4M2R8L | 8765432 | 2012.45 | 52 |
| 2024-03-16T00:00:00 | U-4M2R8L | 11223344 | 2234.67 | 61 |
| 2024-03-15T00:00:00 | U-9F5N1Z | 6543210 | 1876.33 | 28 |
| 2024-03-16T00:00:00 | U-9F5N1Z | 7654321 | 1954.11 | 33 |
| 2024-03-15T00:00:00 | U-3C8W6Q | 10203040 | 2345.90 | 72 |
| 2024-03-16T00:00:00 | U-3C8W6Q | 9182736 | 2109.78 | 68 |
The Challenge
You need this data in Excel — but not as-is. Three things break immediately:
- Excel auto-converts
2024-03-15T00:00:00into15-Mar-24and drops theT00:00:00part — losing all time context - Large step counts become scientific notation (
1.23E+07) because Excel sees them as numbers >10 million and applies default formatting - No headers are included in the raw CSV — so A1 says
2024-03-15T00:00:00, notDate
This isn’t a Fitbit bug. It’s Excel doing exactly what it’s told — and what it’s told is nothing. You must intervene before Excel auto-formats.
Walking Through It
Do this first: Don’t double-click the CSV. Open Excel blank. Go to Data → Get Data → From Text/CSV. Navigate to your file. Click it. Click Import.
Now — here’s the counterintuitive part: In the preview window, click Transform Data. That opens Power Query Editor. This is where you fix everything — before it hits your worksheet.
Step 1: Promote first row to headers
Right-click the first row → Promote Headers. Now A1 = Date, B1 = User ID, etc.
Step 2: Fix the Date column
Select the Date column → Transform → Date → Date Only. But wait — that drops time. Instead, do this: Select Date → Transform → Parse → As DateTime. Then right-click column → Change Type → Date/Time. Done.
Step 3: Fix Steps formatting
Select Steps column → Transform → Format → Number. Then go to Home → Number → Comma Style (Ctrl+Shift+1). Or type =TEXT([Steps],"#,##0") in a new column if you prefer formulas.
Before (raw import):
| Date | User ID | Steps |
|---|---|---|
| 2024-03-15T00:00:00 | U-7XK9P2 | 12345678 |
| 2024-03-16T00:00:00 | U-7XK9P2 | 9876543 |
After Power Query cleanup:
| Date | User ID | Steps |
|---|---|---|
| 2024-03-15 12:00:00 AM | U-7XK9P2 | 12,345,678 |
| 2024-03-16 12:00:00 AM | U-7XK9P2 | 9,876,543 |
The Result
Here’s what your final sheet looks like in Excel — ready for charts, pivot tables, or sharing with managers:
| Date | User ID | Steps | Calories | Active Minutes |
|---|---|---|---|---|
| 2024-03-15 | U-7XK9P2 | 12,345,678 | 2,145.89 | 47 |
| 2024-03-16 | U-7XK9P2 | 9,876,543 | 1,987.22 | 39 |
| 2024-03-15 | U-4M2R8L | 8,765,432 | 2,012.45 | 52 |
| 2024-03-16 | U-4M2R8L | 11,223,344 | 2,234.67 | 61 |
| 2024-03-15 | U-9F5N1Z | 6,543,210 | 1,876.33 | 28 |
| 2024-03-16 | U-9F5N1Z | 7,654,321 | 1,954.11 | 33 |
| 2024-03-15 | U-3C8W6Q | 10,203,040 | 2,345.90 | 72 |
| 2024-03-16 | U-3C8W6Q | 9,182,736 | 2,109.78 | 68 |
What Could Go Wrong
These three mistakes happen every time — and they’re avoidable:
| Symptom | Cause | Fix |
|---|---|---|
| Excel shows '#####' in Date column | Column width too narrow after auto-formatting to Date/Time | Double-click column border (or Alt+H,O,I) |
| All steps show as 0.00 | You applied 'Currency' format instead of 'Number' + comma style | Select column → Ctrl+Shift+1 → then Ctrl+1 → choose 'Number', 0 decimals |
| User IDs like 'U-7XK9P2' turn into 'U-7X9P2' (missing 'K') | Excel auto-converted text to number and dropped letters | Before importing, in Power Query: select User ID column → Transform → Data Type → Text |
Next step: Save your cleaned data as fitbit_clean_2024q1.xlsx. Then use Alt+N,V to insert a pivot table summarizing average steps by user. Filter on Date >= 2024-03-01. Done.
