Migration result

For imported ISO timestamps stored as text, extract the YYYY-MM-DD portion, convert it to a Sheets date, and calculate the ISO week from that date:

=DATEVALUE(LEFT(B2,10))
=ISOWEEKNUM(E2)

Format the first formula column as a date. Keep the original timestamp beside the derived fields so the migration remains auditable.

Test setup

We entered three synthetic rows shaped like a Clockify export: project, start timestamp, end timestamp, and duration. The starts were 2026-01-02T09:00:00Z, 2026-01-05T14:00:00Z, and 2026-12-31T22:00:00Z.

Tested steps

  1. Paste the synthetic export rows into a private Google Sheet.
  2. In a new date column, use DATEVALUE(LEFT(B2,10)) and fill down.
  3. In the next column, use ISOWEEKNUM on the derived date and fill down.
  4. Create a second pair of formula columns using the same source timestamps.
  5. Compare every first-run date/week result with its retest result.

Both runs returned the same date serials and ISO weeks:

Source date Date serial ISO week
2026-01-02 46024 1
2026-01-05 46027 2
2026-12-31 46387 53

Migration checklist

Before replacing the old reporting step, define which timezone owns the broadcast date, confirm the incoming timestamp format, format the derived serial as a visible date, and compare boundary cases around midnight and New Year. Retain the raw timestamp until the new report has reconciled with the old one.

Evidence boundary

This verifies transformation of an exported schema, not live Clockify ingestion or Zapier scheduling. If the source timestamps must be converted from UTC into a local reporting timezone, add and test that conversion before truncating the timestamp to a date.