ClickUp Date Format in Google Sheets: 4 Fixes

Dates sorting alphabetically, due dates off by a day, all-day tasks at 4am, 13-digit numbers. 4 ClickUp date problems in Sheets, and the fix for each.

6 min read
Title card showing a ClickUp due date arriving in a spreadsheet cell in 3 different broken formats on a dark background

Dates are the field every report depends on and the field most likely to arrive broken. 4 distinct problems produce 4 distinct symptoms, and treating one as another wastes an afternoon.

Match your symptom below.

Symptom 1: dates sort alphabetically

April before January. December before February. Sorting by date puts the year in the wrong order.

Cause. The cells hold text, not dates. ClickUp's CSV export defaults to its Normal date format, which produces something like Mon, Aug 11, 2026 3:00 pm +02:00. Google Sheets sees a string and stores a string.

The quick confirmation is =ISNUMBER(A2). A real date is a number underneath and returns TRUE. Text returns FALSE. A second tell is alignment: Sheets right-aligns dates and numbers, left-aligns text. A left-aligned date column has already told you.

Fix, at the source. Re-export with Date Format: ISO. ISO 8601 (2026-08-11T15:00:00.000Z) is the one format Sheets parses into a real date on import, every time, regardless of the reader's locale.

Fix, after the fact. If re-exporting is not an option:

=DATEVALUE(MID(A2, 6, 12))

adjusting the offsets to your string, then format the result as a date. This is fragile and locale-dependent, which is why fixing the export is worth the extra 30 seconds.

One ClickUp due date exported 3 ways. Normal, the default, arrives as the text Mon, Aug 11, 2026 3:00 pm plus 02:00 and fails ISNUMBER. ISO arrives as 2026-08-11T15:00:00 and passes. POSIX arrives as the 13 digit number 1786554000000 and sorts correctly only after converting

Symptom 2: a column of 13-digit numbers

1786554000000 where you expected a date.

Cause. POSIX time, in milliseconds since 1 January 1970. This is what the POSIX export option produces, and it is what the ClickUp API always returns, with no alternative. Any Apps Script implementation meets this on the first task.

Fix. Divide out the milliseconds and add the epoch:

=A2/86400000 + DATE(1970,1,1)

then format the cell as a date. In Apps Script, new Date(Number(field.value)) does the same thing.

A caveat that matters: this converts to UTC. If your workspace thinks in local time, see symptom 3.

Symptom 3: everything is off by a day, or shows 4am

A task due 11 August shows as 10 August. Or every all-day task shows a time of 4:00 am, or midnight, when nobody set a time.

Cause, part 1: the timezone. ClickUp stores an instant. The API hands you a millisecond count with no timezone attached, so whatever converts it picks one. Google Sheets applies the spreadsheet's timezone, which is set in File, Settings and is not necessarily yours. A task due at 9am in UTC+2 is 07:00 UTC, and a sheet set to UTC-5 renders that as 02:00 on the same day. Push the due time later in the day and the date itself rolls backwards.

Cause, part 2: 4am is a value ClickUp stores. ClickUp's own ClickUp date formats doc says it plainly: "Date fields without specific times are set to 4 am in the local timezone of the person setting the field." The hour is in the data, waiting for anything that renders a timestamp. 2 people in different timezones setting the same due date store different instants. Depending on the timezone gap, those 2 readings can sit 4 hours apart from midnight in each of their days.

ClickUp tracks separately whether that time component is meaningful. A task due "on Tuesday" and one due "Tuesday at 5pm" are both a timestamp; only the due_date_time boolean distinguishes them. Anything that ignores that flag renders every all-day task at 4am, or at whatever local hour the timestamp lands on after conversion.

A task due 11 August at 01:00 in a UTC+2 workspace, stored as 2026-08-10T23:00Z, rendered in 3 spreadsheet timezones. The matching timezone shows 11 August, while UTC and UTC minus 5 both show 10 August. Below, the same date with the all day flag read, giving a bare 2026-08-11, and ignored, giving 2026-08-11 04:00

Fix.

  1. Set the spreadsheet timezone to match the workspace: File, Settings, Time zone. Do this before anything else, because it silently changes every date already in the file.
  2. For date-only reporting, strip the time so the comparison cannot drift: =INT(A2) gives the date part alone.
  3. If you are writing the sync, read due_date_time and render all-day dates as a bare date rather than a timestamp.

The general rule: pick date-only or timestamp for a given column and never mix them. A column with both is a column where = comparisons fail for reasons nobody can see.

Symptom 4: dates were fine, now they are not

Everything worked. After a re-import, half the column is text.

Cause. Re-importing over an existing tab re-runs the parse, and Google Sheets' import dialog has a Convert text to numbers, dates, and formulas checkbox that is remembered per import rather than per file. With it toggled off, dates land as text. With it toggled on, it can go further than you wanted and rewrite things that were meant to stay strings, like a task id of 0012 becoming 12.

Fix. Watch the checkbox on every import. Better, stop re-importing: a sync that writes into the tab directly writes typed values rather than parsing text, so this whole class of problem goes away. That is one of the underrated reasons to move off CSV once a report becomes recurring.

Changing the date format in ClickUp, and what that setting reaches

There is a date format setting in ClickUp, and it is the first thing most people go looking for. Click your avatar in the upper-right corner, then Settings, then the Time & Date format section, where you can set the start of the calendar week, the time format, and the date format. It is available on every plan.

2 things about it are worth knowing before you spend time there:

It is per account, not per workspace. ClickUp states that the change "will not affect the time and date settings of other users in your Workspace." Your colleague's screenshot and yours can disagree about the same due date, and neither is wrong.

It changes the display in ClickUp only. The CSV export has its own Date Format dropdown (Normal, ISO, POSIX) that ignores your account setting, and the API returns millisecond timestamps whatever anyone has selected. So the setting fixes what you read on screen and reaches nothing on this page. The export dialog walkthrough covers where the choice that does matter lives.

The 5-minute preflight

Before building anything on a date column:

=ISNUMBER(A2)     → must be TRUE
=INT(A2)=A2       → TRUE means date-only, FALSE means it carries a time
=TEXT(A2,"yyyy-mm-dd hh:mm")  → read it back and check against ClickUp

3 cells, and they catch every problem on this page before it reaches a chart.

Getting it right by default

If dates are your recurring pain, the shortest route out is to stop parsing text. The ClickUp to Sheets add-on writes typed date values straight into the cells, honouring the all-day flag, so there is no import step and no format choice to get wrong. If you are writing it yourself, the ClickUp API guide shows the conversion in place.

For what happens to the rest of your fields, ClickUp custom fields in Google Sheets goes type by type.