Convert UTC comment timestamps to your time zone, and chart comments per hour
Every timestamp in a comment export is in UTC. That keeps files from four networks consistent, and it means 9 pm in New York shows as 01:00 or 02:00 the next day. Before asking "when does my audience comment", move the times to the audience's clock. It takes one formula, and one more if daylight saving matters.
Updated Nov 19, 2026 · 6 min read
First, make sure the timestamp is a real date
posted_at_utc is column F. How it arrives depends on the route:
- In the Excel workbook it is a real date and time cell, shown as
2026-10-02 14:03:00. - Through Copy for Sheets or a live =IMPORTDATA link, Google Sheets recognises the format and makes it a date.
- In a CSV imported with type detection off, it stays text.
Test one cell with =ISNUMBER(F2). Dates are numbers underneath, so TRUE means a real date. To be safe either way, start from a helper column that accepts both. Column M is platform, so use N, with the header utc:
=IF(ISNUMBER(F2), F2, DATEVALUE(LEFT(F2, 10)) + TIMEVALUE(MID(F2, 12, 8)))
It works in Excel and Google Sheets. Format the column as date and time. Everything below reads from N.
A fixed offset
Spreadsheets store a date as a count of days, so one hour is 1/24. To move a time by a number of hours, add that number divided by 24. In O1 type local, and in O2:
=N2 + (-5)/24
Replace -5 with your zone's offset from UTC:
| Zone | Standard time | Summer time |
|---|---|---|
| Los Angeles | -8 | -7 |
| New York, Toronto | -5 | -4 |
| São Paulo, Buenos Aires | -3 | -3 |
| London, Lisbon | 0 | 1 |
| Madrid, Paris, Berlin | 1 | 2 |
| Dubai | 4 | 4 |
| India | 5.5 | 5.5 |
| Sydney | 10 | 11 |
Half-hour zones work the same way: =N2 + 5.5/24 for India. Putting the offset in a cell of its own, say $T$1, and writing =N2 + $T$1/24 lets you switch zones by changing one number.
A fixed offset is right when all the comments fall in one season, which is true for most single posts. Look at the first and last dates in the file. If a clock change falls between them, use the next section.
An offset that follows daylight saving
Neither Excel nor Google Sheets has a time zone function, so the changeover dates go into the formula. Clocks change at a fixed UTC moment, which makes the test simple. For New York in 2026, summer time runs from 8 March at 07:00 UTC to 1 November at 06:00 UTC:
=N2 + IF(AND(N2 >= DATE(2026,3,8) + 7/24, N2 < DATE(2026,11,1) + 6/24), -4, -5) / 24
For Madrid, Paris or Berlin in 2026, summer time runs from 29 March at 01:00 UTC to 25 October at 01:00 UTC:
=N2 + IF(AND(N2 >= DATE(2026,3,29) + 1/24, N2 < DATE(2026,10,25) + 1/24), 2, 1) / 24
Both formulas work in Excel and Google Sheets. For London and Lisbon use the same dates with 1 and 0. Southern-hemisphere zones are the other way round: summer time covers the turn of the year, so swap the two offsets and use that zone's dates.
For exports that span several years, keep a small table of start and end moments per year on another tab and look the offset up. For one campaign, the single formula is enough.
One limit that no formula removes: the offset describes one place. If your audience is spread across continents, "local time" is a choice you make, usually the zone where most customers are. Say which one on the chart.
Hour and weekday columns
From the local time in O, in both apps:
| Column | Header | Formula | Gives |
|---|---|---|---|
| P | hour | =HOUR(O2) | 0 to 23 |
| Q | weekday | =WEEKDAY(O2, 2) | 1 for Monday to 7 for Sunday |
| R | day | =INT(O2) | The local date, formatted as a date |
The day column fixes a subtle error in per-day counts. The workbook's Per day sheet and any pivot on posted_at_utc count UTC days. For an audience in Los Angeles, a single evening is split across two UTC dates. Pivot on day instead and each evening is whole. The steps for the per-day chart are in how to chart comments per day.
Count comments per hour
On a new tab, type the hours 0 to 23 in A2 to A25. In B2, copied down:
=COUNTIF(Comments!P:P, A2)
That is every comment and reply in each hour. To count only top-level comments, which shows when people arrive at the post and not when they argue under it:
=COUNTIFS(Comments!P:P, A2, Comments!H:H, "no")
Both work in Excel and Google Sheets, and the list of 24 hours means hours with no comments show as 0 instead of going missing, which a pivot table would do. In Google Sheets the whole table can also come from one formula:
=QUERY(Comments!A:R, "select P, count(P) where P is not null group by P order by P label count(P) 'comments'", 1)
Select the two columns and insert a column chart. Put the zone in the title: "Comments per hour, New York time".
An hour-by-weekday grid
With hours 0 to 23 down column A (from A2) and the weekday numbers 1 to 7 across row 1 (from B1), put this in B2 and copy it across and down:
=COUNTIFS(Comments!$P:$P, $A2, Comments!$Q:$Q, B$1)
Select the grid and apply a colour scale with conditional formatting. The dark cells are when your audience comments. The same grid as a pivot table is pivot 5 in pivot tables for comment exports.
What the chart can and cannot tell you
- One post mostly shows its own launch. Comments pile up in the hours after publishing whatever time that was. A post that went up at 6 pm will "prove" your audience is active at 6 pm. To see the audience's own rhythm, stack exports of posts published at different times of day. The stacking method is in compare comments across networks in one sheet.
- Hours since posting is often the better axis. In a spare column:
=(N2 - MIN(N$2:N$5000)) * 24gives each comment's age in hours, measured from the first comment. A chart of that shows how quickly a post gathers its comments and when it stops, which is what decides when to export and when to reply. - Networks keep different hours. TikTok comments keep arriving for days while a video is still being recommended. Compare per-hour charts by network using the
platformcolumn. - A partial export bends the curve. A file that holds only the first comments shows only the first hours. Check comments read against the displayed count first: how to check an export is complete.
For a chart other people can filter by date and network, feed the same columns to a dashboard: build a comment dashboard in Looker Studio.
FAQ
Why are the timestamps in UTC?
UTC is the same everywhere and has no daylight saving, so files from any network and any country line up. Converting to one local zone is a choice that depends on your audience, so it is left to you.
How do I convert UTC to my time zone in Excel?
Add the offset in hours divided by 24 to the timestamp: =F2 + (-5)/24 for UTC minus 5. Format the result as date and time.
Does Google Sheets convert time zones automatically?
No. The spreadsheet time zone setting affects functions such as NOW, not timestamps you import. Add the offset with a formula.
What about daylight saving time?
Use an IF that tests whether the UTC time falls between the two changeover moments of the year and picks the summer or the standard offset. The formulas for New York and central Europe in 2026 are in this guide.
Why does my formula return #VALUE!?
The timestamp is text and was used in arithmetic directly. Build the helper column with DATEVALUE and TIMEVALUE first, then add the offset to that.