The date() and timestamp() functions in Attio: turning date-shaped text back into dates you can actually filter

Every other function in Attio's library consumes typed data. You give dateDiff() two dates, sum() a set of numbers, lower() some text, and it does something with what you handed it.

date() and timestamp() run the other direction. They take a value that is not a date — a string from a CSV import, a number from a webhook payload, a field an enrichment provider filled in — and give it back the type it should have had all along. They are the repair functions, and they are the reason a migration that "went fine" can still leave you unable to build a single useful view.

The thing that makes this worth an article rather than a sentence is that the damage is invisible. A text attribute holding 2024-01-15 and a date attribute holding 2024-01-15 look exactly the same in the table. You find out which one you have when you try to do something.

This is part of our function-by-function series on Attio formula attributes — previously: if(), timeSpentIn(), dateDiff(), the ?? operator, count(), hasBeenIn(), valueSetAt(), contains(), sum(), previousValue(), dateAdd(), formatDate(), min()/max(), valueAt(), replace()/replaceAll(), avg()/median(), round()/ceil()/floor(), and()/or()/not(), length(), unique(), abs(), today()/now(), eomonth(), day()/hour(), mod(), power(), exp()/log(), split()/splitPart(), startsWith()/endsWith(), lower()/upper(), and left()/right().

Table of contents

• What date() and timestamp() do
• A date-shaped string is not a date
• Why ISO text is the format that fools you
• Label or moment: choosing between the two
• Parsing the numbers integrations send
• There is no format argument
• Rebuilding a non-ISO date as arithmetic
• The first function in the library that fails loudly
• Never delete the raw column
• Literal dates inside other formulas
• What Attio has quietly added to the date library
• CRM use cases
• Copy-paste formulas
• Gotchas
• Final thoughts

What date() and timestamp() do

One argument each. Give them something date-shaped, get a real date type back.

date(raw_date)
timestamp(value)

date() parses a number or text value into a date, accepting ISO 8601 date strings or numeric timestamps. timestamp() parses a text or number value into a timestamp, accepting ISO 8601 datetime strings or numeric Unix timestamps.

Attio's own examples:

date("2024-01-15")                  →   2024-01-15
timestamp("2024-01-15T09:30:00Z")   →   2024-01-15T09:30:00Z

In practice you almost never pass a literal. You pass the text attribute your import left behind:

date({Imported close date})
timestamp({Raw created at})

That formula attribute is now a real date. Everything that was impossible five minutes ago is available on it.

A date-shaped string is not a date

This is the part worth internalising, because nothing in the interface will tell you. Here is what actually changes when the type is right.

Note how much of that list is reporting rather than data entry. A text date does not stop anyone recording information. It stops you asking questions of it afterwards, which is a cost that lands weeks later on somebody who was not involved in the import.

The second thing worth noticing is the last row. Emptiness works either way, which is exactly why nobody catches this during a data-quality pass. The column is populated, it is spelled correctly, and it is useless.

Why ISO text is the format that fools you

ISO 8601 sorts correctly as plain text. Not because anything understands it, but because of how it is built: the units run in descending order of significance, and every one of them is zero-padded to a fixed width. So "2024-01-15" really does come before "2024-02-01" under a dumb character-by-character comparison, and a text column full of ISO dates will sort into perfect chronological order.

That is the trap. The column half-works. Sorting is the first thing anyone tries, sorting is fine, and so the column is never suspected. It gets used for months before somebody tries to filter on "closed in the last quarter" and discovers the option is not there.

Every other format fails immediately and loudly, which is a kindness by comparison:

"15/01/2024"   sorts under 1
"01/12/2024"   sorts under 0, so it lands first
"Jan 15, 2024" sorts alphabetically, so April leads the year

Nobody ships a report off a column that sorts like that. They do ship reports off ISO text.

The practical rule: if you inherited a column of ISO date strings, it is not evidence that the import went well. It is the one case where the broken thing looks correct.

Label or moment: choosing between the two

The obvious way to pick between date() and timestamp() is to look at the string — has it got a time in it? That is the wrong question, because it tells you what the source system happened to store rather than what you need.

The right question is whether the value is a label or a moment.

A label is a calendar day that means the same thing everywhere on earth. A contract start date. A close date. A renewal. An invoice date. A birthday. Nobody's contract starts at 00:00 UTC; it starts on the fifteenth, and it starts on the fifteenth in Sydney and in San Francisco. Use date(), and if the source string carries a time component, throw it away on purpose.

A moment is a point on the global timeline. A meeting. The instant a form was submitted. The start of an SLA clock. A webhook event. These genuinely differ by timezone, and flattening them to a day destroys information you will want. Use timestamp().

Getting this backwards in the label direction is the expensive one. Take a timestamp where you meant a date and you have imported a timezone into a field that has no business having one — and as the day() and hour() deep dive covers, the extractors read timestamps in UTC, so a contract "starting" at 2024-01-15T23:00:00-05:00 will report its day as the sixteenth to every formula that touches it. You will chase that off-by-one date through three downstream fields before you find it.

Parsing the numbers integrations send

Both functions take numbers as well as strings, and that path matters more than the documentation makes it sound. Integrations, webhooks and APIs overwhelmingly send Unix time, and when that lands in Attio through a workflow or a sync it often lands in a number attribute. It is not corrupted data. It is just unparsed.

timestamp({Raw created at})

The one thing to check is the unit, because there are two conventions in circulation and they differ by a factor of a thousand:

1705311000        ten digits    seconds        the Unix convention
1705311000000     thirteen digits    milliseconds    what most JavaScript emits

If you feed a millisecond value to something expecting seconds, the result is not a subtle error — it lands roughly fifty thousand years in the future, which is at least easy to spot. Count the digits before you trust the output, and scale if you need to:

timestamp({Raw created at} / 1000)

Anything that came out of a browser, a Node service or most modern SaaS webhooks is a good bet for milliseconds. Anything from a Unix-flavoured backend, a database column or a POSIX API is a good bet for seconds. Checking the digit count takes five seconds and saves a very confusing afternoon.

There is no format argument

Here is the asymmetry at the centre of this whole topic.

13px] bg-[color:var(--color-bg-muted)] border border-[color:var(--color-border)] px-1.5 py-0.5 rounded">formatDate() takes a format string on the way out. You tell it exactly how you want the date rendered, token by token, and as the [formatDate() deep dive covers in detail, those tokens are precise and case-sensitive.

date() takes no format string on the way in. There is no date(value, "dd/MM/yyyy"). There is no locale setting. The function accepts ISO 8601, and that is the entire contract.

So if your source column contains 03/04/2024, you cannot tell Attio that you mean the third of April. Attio has no regex anywhere in its function library, so you cannot pattern-match your way out either. And — this is the bit that closes off the obvious workaround — there is no concat() function and no string-joining operator for text. Which means the repair everybody reaches for, the one that takes ten seconds in a spreadsheet, is simply not available:

split on "/", take the pieces, glue them back together as "yyyy-MM-dd", parse that

You can do the splitting. splitPart() will hand you each component perfectly well. You cannot do the gluing. There is nothing to glue with.

The honest conclusion is that a non-ISO date string should be fixed before it reaches Attio — reformat the CSV column, or change the mapping in the sync — and that is genuinely the right answer whenever you control the source.

When you do not control the source, there is one way through, and it is not a string operation at all.

Rebuilding a non-ISO date as arithmetic

If you cannot build the date as text, build it as a date and count to it.

Pick a fixed anchor date, extract the three components as numbers, and walk 13px] bg-[color:var(--color-bg-muted)] border border-[color:var(--color-border)] px-1.5 py-0.5 rounded">dateAdd() out from the anchor three times. For a dd/MM/yyyy string — remembering that [splitPart() indexes from zero, so day is index 0, month is index 1 and year is index 2:

dateAdd(
  dateAdd(
    dateAdd(date("2000-01-01"),
            number(splitPart({Raw date} ?? "", "/", 2)) - 2000, "years"),
    number(splitPart({Raw date} ?? "", "/", 1)) - 1, "months"),
  number(splitPart({Raw date} ?? "", "/", 0)) - 1, "days")

Read it from the inside out. Start at 1 January 2000. Add the year offset to land on 1 January of the right year. Add the month offset — one less than the month number, because January is already where you are — to land on the first of the right month. Add the day offset, again one less, to land on the day itself.

Tracing 15/01/2024: the anchor plus 24 years is 2024-01-01, plus 0 months is 2024-01-01, plus 14 days is 2024-01-15. Tracing 03/04/2024: plus 24 years, plus 3 months is 2024-04-01, plus 2 days is 2024-04-03. Correct, and unambiguous in a way the original string never was.

Three things to know before you use it.

The three subtractions are not decoration. Every one of them exists because you are starting from a real date rather than from zero, and the anchor already contributes a year, a month and a day. Drop one and everything shifts by a month or a day, consistently, on every record — which is exactly the kind of error that survives a spot check.

Change the indexes, not the structure, for other formats. mm/dd/yyyy is the same formula with indexes 1 and 0 swapped. A dotted German format is the same formula with "." as the delimiter. The shape does not change.

color:var(--color-text-heading)]">Keep it in one formula attribute. It is tempting to break this into three — one per component — and reference them. Do not. As covered in the [dateAdd() deep dive, a formula attribute that references another formula attribute returns wrong values with no error at all, and will even return a value on rows where the referenced field is blank. Nested function calls are fine and this formula has plenty of them. Chained formula attributes are not. Inline it, however ugly it looks.

It is ugly. It is also exact, and it runs on every record forever without anyone re-exporting anything.

The first function in the library that fails loudly

Worth pausing on number(), because it breaks a pattern this series has been tracking for weeks.

Attio's text functions fail silently, almost without exception. An out-of-range 13px] bg-[color:var(--color-bg-muted)] border border-[color:var(--color-border)] px-1.5 py-0.5 rounded">splitPart() index returns empty text rather than an error. A case-mismatched endsWith() returns false, which reads exactly like a real negative. A left() count of zero or less [returns empty text and quietly empties the column for every short record. In each case the formula succeeds and the answer is wrong, which is the worst combination available.

number() does not do that. The documentation is explicit: if the value cannot be parsed as a number, the formula returns an error.

That is good news, and you should not suppress it. An errored cell in the reassembly above is a row whose date string is not the shape you assumed — a stray header row, a blank, a "N/A", a European date hiding in an American column. Wrapping the whole thing in an if() with a sentinel fallback converts a visible problem into an invisible one and buys you nothing. Filter the view to the errored records, fix those, and let the formula keep telling you the truth about the rest.

Never delete the raw column

The instinct after building a working parse is to tidy up: the text column is redundant now, so delete it.

Do not, and the reason is specific. A date attribute that is empty because the source was empty and a date attribute that is empty because the parse gave up are indistinguishable. Both are blank cells. You have lost the ability to tell "this deal never had a close date" from "this deal had one we could not read", and those need completely different responses.

Keep the source text and you keep the diagnostic:

length({Imported close date} ?? "") > 0

Set that as a checkbox and any record where it is true while the parsed date is empty is a parse failure, not a data gap. It costs one attribute and it turns an unanswerable question into a filterable list.

The storage argument for deleting it is not real. The audit trail argument for keeping it is.

Literal dates inside other formulas

Not every use of date() is a repair job. Its other role is quieter and probably more common: constructing a fixed date inside a formula that needs one.

valueAt({Deal stage}, date("2026-07-01"))

That is the point-in-time snapshot from the valueAt() deep dive — what stage was every deal in at the start of the quarter — and it only works because date() turns a string literal into a date the function will accept. The same applies anywhere you need a hard boundary rather than a rolling one:

dateDiff(date("2026-01-01"), {Close date}, "days")
{Close date} >= date("2026-07-01")

Fiscal year starts, cohort cutoffs, contract effective dates, the day you migrated CRMs. All of them are literal dates, and date("...") is how you say them.

One caution: a hard-coded date is a maintenance item. date("2026-01-01") in a "days into the fiscal year" formula will be silently wrong on 2 January 2027 — not broken, not erroring, just answering a question nobody asked any more. If the boundary moves every year, derive it rather than typing it.

What Attio has quietly added to the date library

While verifying this article against the live functions library, we found the date and time section has grown beyond what most documentation and every third-party article currently reflects. Alongside day() and hour(), there are now five more extractors:

13px] bg-[color:var(--color-bg-muted)] border border-[color:var(--color-border)] px-1.5 py-0.5 rounded">quarter() is the useful one, and it is worth understanding how it differs from the approach the [formatDate() article recommends. formatDate({Close date}, "'Q'Q yyyy") gives you the text label Q1 2026, which is what you want on a report axis or a grouped view. quarter({Close date}) gives you the number 1, which is what you want when the quarter needs to be compared, filtered numerically or fed into further arithmetic. Different jobs, and now you have both.

There is also a full color:var(--color-text-heading)]">type conversion category that the same verification turned up — text(), number(), currency(), checkbox(), rating() and phoneNumber() — and ifNull(), a two-argument function form of the [?? operator. number() is the one doing real work in this article. The others each deserve their own treatment and will get it.

We have updated our formula attributes reference to cover all of them.

CRM use cases

• Rescue a migration. date({Imported close date}) on every date column a CSV import left as text. This is usually the single highest-value hour of post-migration cleanup, because it unlocks every report at once rather than one at a time.
• Make webhook data behave. timestamp({Raw created at}) turns the epoch numbers an integration writes into timestamps that dateDiff() will accept, which is the difference between having a speed-to-lead metric and not.
• Quarter cohorts on imported data. quarter(date({Imported close date})) — group historical deals by quarter without touching the source.
• Fiscal boundaries. {Close date} >= date("2026-07-01") as a checkbox, so a view can separate this fiscal year from everything before it without a rolling filter.
• Point-in-time reporting. valueAt({Deal stage}, date("2026-07-01")) for pipeline-movement analysis against a fixed date.
• Parse-failure queue. length({Imported close date} ?? "") > 0 beside the parsed column, so failed parses are a filterable list rather than an assumption.

Copy-paste formulas

Parse an imported text date:

date({Imported close date})

Parse an integration's datetime string:

timestamp({Raw created at})

Parse a millisecond epoch number:

timestamp({Raw created at} / 1000)

Rebuild a dd/MM/yyyy string as a real date:

dateAdd(
  dateAdd(
    dateAdd(date("2000-01-01"),
            number(splitPart({Raw date} ?? "", "/", 2)) - 2000, "years"),
    number(splitPart({Raw date} ?? "", "/", 1)) - 1, "months"),
  number(splitPart({Raw date} ?? "", "/", 0)) - 1, "days")

Same thing for mm/dd/yyyy — only the indexes change:

dateAdd(
  dateAdd(
    dateAdd(date("2000-01-01"),
            number(splitPart({Raw date} ?? "", "/", 2)) - 2000, "years"),
    number(splitPart({Raw date} ?? "", "/", 0)) - 1, "months"),
  number(splitPart({Raw date} ?? "", "/", 1)) - 1, "days")

Flag records whose source had content, so you can find parse failures:

length({Imported close date} ?? "") > 0

Sales cycle length on a rescued date column:

dateDiff({Created at}, date({Imported close date}), "days")

Fiscal-year checkbox from a literal boundary:

{Close date} >= date("2026-07-01")

Quarter as a number, for filtering and arithmetic:

quarter({Close date})

Gotchas

• ISO 8601 only. date() has no format argument, no locale setting, and Attio has no regex. Anything that is not ISO has to be fixed upstream or rebuilt with arithmetic.
• There is no concat(). You can split a date string into parts. You cannot join them back into one. This is why the string-repair approach everybody tries first does not exist.
• Count the digits on epoch numbers. Ten is seconds, thirteen is milliseconds. A millisecond value parsed as seconds lands tens of thousands of years out.
• date() for labels, timestamp() for moments. A timestamp drags a timezone along with it, and the extractors read it in UTC, so a late-evening local time reports as the next day.
• A text date column that sorts correctly is still a text date column. ISO sorts right by accident. Sorting is not a type test.
• number() errors rather than going quiet. Unlike the text functions, a bad parse is visible. Do not wrap it in an if() to hide it — filter on it and fix the rows.
• Do not chain formula attributes. Build the whole parse in one formula. A formula that references another formula returns wrong values with no error, including on rows where the referenced field is blank.
• Guard blanks at the attribute. splitPart({Raw date} ?? "", "/", 0), never splitPart({Raw date}, "/", 0) ?? "".
• Keep the raw column. Without it you cannot distinguish an empty source from a failed parse.
• Hard-coded literals expire. date("2026-01-01") will still be answering last year's question next January, silently and confidently.
• Force the output type. Set the formula attribute to Date or Timestamp explicitly rather than leaving it on Auto, so downstream filters get what they expect.

Final thoughts

date() and timestamp() are the least interesting functions in Attio's library and among the most consequential, because they sit at the boundary where data stops being a picture of a date and starts being one.

The lesson underneath them is about imports rather than formulas. Almost every text date column in a CRM got there the same way: a migration ran, the mapping defaulted to text on a column nobody checked, the values looked right in the table, and the cost arrived weeks later as a report that could not be built. A formula attribute is a genuine fix for that — it is live, it is exact, and it costs one attribute. But it is a fix applied at the wrong end of the pipe, and the second column always follows the first.

If you are writing 13px] bg-[color:var(--color-bg-muted)] border border-[color:var(--color-border)] px-1.5 py-0.5 rounded">date() around a field that should simply have been a date, that is a schema conversation rather than a formula one — and it is one of the first things a [workspace audit looks for, because it is cheap to find and expensive to leave.

Next: setTimezone(), and why the timezone a timestamp displays in is a different question from the timezone it is stored in.

Migrating into Attio, or cleaning up after a migration that left this behind? Our AI-native Attio sprint covers the data model, the import mapping and the formulas as one job.