The day() and hour() functions in Attio: finding the rhythm hiding in your timestamps

Every date function covered in this series so far measures distance. dateDiff() measures the gap between two moments. dateAdd() moves a fixed distance forward. eomonth() snaps to the edge of a period. All of them care where a date sits on the timeline.

day() and hour() throw the timeline away. They take a date and return a single component of it as a number — which day of the month, which hour of the day — and in doing so they collapse three years of records onto the same 31 days, or the same 24 hours. That collapse is the point. It's how a thousand individually unremarkable timestamps turn into the sentence "43% of our inbound arrives after 5pm and nobody is on it until the morning."

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(), and eomonth(). These two travel together because they're the same idea at two scales.

Table of contents

• What day() and hour() do
• Position in time vs position in a cycle
• The hour is UTC until you say otherwise
• Lead arrival patterns and coverage gaps
• Billing day of month, and the 31st problem
• The missing extractors
• Composing with the rest of the library
• CRM use cases that earn their keep
• Copy-paste formulas
• Gotchas
• Final thoughts

What day() and hour() do

Both take a date and return a number.

day(date("2024-03-15"))                       →   15
hour(timestamp("2024-03-15T14:30:00Z"))       →   14

day() gives the day of the month, 1 to 31. hour() gives the hour of the day, 0 to 23 — a 24-hour clock, so 14 is 2pm and midnight is 0, not 24.

The important word in both cases is number. Not a date, not a label. That's what separates these from formatDate(), which can also show you a day or an hour but hands back text. A number you can compare with >, feed to and(), average across records, and filter numerically. Text you can only match.

Position in time vs position in a cycle

There are two completely different questions you can ask about a timestamp, and most CRM setups only ever ask the first one.

*Where is this on the timeline?* — how old is the lead, how long until renewal, which quarter did it close in. That's dateDiff(), dateAdd(), eomonth(). Chronology.

*Where is this in the cycle?* — what hour of the day, what day of the month. That's day() and hour(). Rhythm.

Chronology tells you about one record. Rhythm only means anything in aggregate — a single lead arriving at 18:00 is noise, four hundred of them is a staffing decision. So these two functions are unusual in this library: their value shows up in a grouped view or a report, not on the record page. Ship them expecting to look at the column sorted and counted, not read one row at a time.

The hour is UTC until you say otherwise

This is the trap, and it catches nearly everyone once.

hour() reads the hour off the timestamp it's given. Attio's own example is explicit about it — hour(timestamp("2024-03-15T14:30:00Z")) returns 14, and that Z is UTC. So if your team is in New York and you write:

hour({Created at})

you get a distribution shifted by four or five hours depending on the season, and it will look plausible. A believable-but-wrong chart is worse than an obviously broken one. Nobody questions "our leads peak at 2pm" — they just staff for it.

Convert first:

hour(setTimezone({Created at}, "America/New_York"))

Now the number means what a person in that office would call the time. Pick the timezone deliberately: your team's, for staffing and coverage questions; the prospect's, if you're asking when *they* are awake. Those are different analyses and they'll give different answers.

The same caveat is milder for day() — the day of the month only shifts across a timezone boundary for records created within a few hours of midnight — but it's the same fix when it matters.

Lead arrival patterns and coverage gaps

The single highest-value thing to do with hour() is find out when work arrives, versus when anyone is there to do it.

Start with a bucket, so the column is readable in a grouped view rather than being 24 separate values:

if(hour(setTimezone({Created at}, "Europe/London")) < 6, "Overnight",
if(hour(setTimezone({Created at}, "Europe/London")) < 12, "Morning",
if(hour(setTimezone({Created at}, "Europe/London")) < 18, "Afternoon", "Evening")))

Group your inbound leads by that field and you have the coverage question answered in one view. If "Evening" and "Overnight" together are a fifth of your volume and your first-touch SLA assumes business hours, you've found where the leads are dying.

The blunt binary version, for a flag you can filter and route on:

or(hour(setTimezone({Created at}, "Europe/London")) < 9,
   hour(setTimezone({Created at}, "Europe/London")) >= 18)

Note the 13px] bg-[color:var(--color-bg-muted)] border border-[color:var(--color-border)] px-1.5 py-0.5 rounded">or(). An out-of-hours window that crosses midnight is *not* a range — you cannot write hour(...) >= 18 and hour(...) < 9, because no number is both. Hour values wrap, and any window spanning midnight is two conditions joined with [or(). This is the single most common bug in hour-based logic.

Layer a value check on top and you get the alert that actually deserves someone's evening:

and(hour(setTimezone({Created at}, "America/New_York")) >= 17, {Deal value} > 50000)

Billing day of month, and the 31st problem

day() earns its keep in anything with a recurring cycle — billing runs, dunning, usage resets, check-in cadences.

day({Contract start})

That's the billing day of month for each customer, as a number, so you can group your book by it and see the load: if 60% of your contracts started on the 1st, your finance team's month has one very bad morning in it.

But the number 1–31 hides a real problem. A billing day of 29, 30 or 31 does not exist in every month. February has neither the 30th nor the 31st, and only sometimes has the 29th. Any system that stores "bill on day 31" has to invent an answer four times a year, and different systems invent different ones.

Handle it explicitly, using eomonth() from the last article as the safe fallback:

if(day({Contract start}) > 28, eomonth({Contract start}), {Contract start})

Anything on the 1st through the 28th keeps its date, because those days exist everywhere. Anything later snaps to month-end, which also exists everywhere. One line, and the ambiguity is gone.

The other half-month split is worth having too, for anyone forecasting cash timing:

if(day({Invoice date}) <= 15, "First half", "Second half")

The missing extractors

There is no month() in Attio's formula library. No year(), no weekday(), no minute(). day() and hour() are the two you get.

For a month or a year, the route is formatDate():

formatDate({Close date}, "MM")      →   "03"
formatDate({Close date}, "yyyy")    →   "2026"

That works, and for grouping and labelling it's the right tool. But be clear about what you're holding: text. "03" is a string, so it won't compare numerically, won't average, and sorts as text — which is fine for zero-padded months and quietly wrong the moment you strip the padding. If you need a real number out of a month, Attio's library doesn't offer a direct route, and the honest answer is to do that work in a report or an export rather than forcing it in a formula.

Day of the week has the same shape — there's no weekday(), and comparing formatted day names is string matching, not arithmetic. Worth knowing before you plan a "weekend leads" project around a function that doesn't exist.

Composing with the rest of the library

day(eomonth({Date})) remains the neatest trick in the pair: eomonth() gives the last day of the month as a date, and day() pulls the number off it. That's the month's length, computed rather than looked up, and it's the correct denominator for any proration.

CRM use cases that earn their keep

When inbound actually arrives. Bucket hour() in your team's timezone, group your leads by it, and compare against when anyone is working. The gap is either a routing rule or a hiring case.

Out-of-hours routing on value. Not everything after 6pm deserves an alert. and() the hour flag with a deal-size or ICP-fit test and only the ones that matter escalate.

Prospect-side timing. Run hour() in the *prospect's* timezone on meeting and email timestamps to find when your best accounts are responsive — and stop sending sequences into their 4am.

Billing load by day. day({Contract start}) grouped across the book shows where the month's operational spikes are before finance complains about them.

Safe recurring dates. The > 28 fallback, applied once, removes an entire category of end-of-month billing incident.

Proration denominators. day(eomonth({Date})) instead of a hardcoded 30.

Copy-paste formulas

day({Contract start})

hour({Created at})

hour(setTimezone({Created at}, "America/New_York"))

if(hour(setTimezone({Created at}, "Europe/London")) < 6, "Overnight", if(hour(setTimezone({Created at}, "Europe/London")) < 12, "Morning", if(hour(setTimezone({Created at}, "Europe/London")) < 18, "Afternoon", "Evening")))

or(hour(setTimezone({Created at}, "Europe/London")) < 9, hour(setTimezone({Created at}, "Europe/London")) >= 18)

and(hour(setTimezone({Created at}, "America/New_York")) >= 17, {Deal value} > 50000)

if(day({Contract start}) > 28, eomonth({Contract start}), {Contract start})

if(day({Invoice date}) <= 15, "First half", "Second half")

day(eomonth({Contract start}))

formatDate({Close date}, "yyyy")

Gotchas

hour() is UTC unless you convert it. This is the one that produces confident, wrong conclusions. Wrap the date in setTimezone() before extracting, and be deliberate about whose timezone you picked — yours and the prospect's answer different questions.

Windows that cross midnight need or(), not a range. Hours wrap from 23 to 0. >= 18 and < 9 is satisfied by no number at all, and the field will sit there returning false forever without erroring.

Midnight is 0, not 24. An "after 6pm" test written as hour(...) > 18 silently drops everything in the 18:00 hour, and >= 24 never matches anything.

Day 29, 30 and 31 don't exist in every month. Any recurring schedule keyed on day() above 28 has to define its own fallback. Snapping to eomonth() is the simplest one that's correct everywhere.

There is no month(), year(), weekday() or minute(). formatDate() covers months and years but returns text — no arithmetic, no numeric sort. Check the function exists before planning work around it.

These are aggregate tools on a per-record field. One row's arrival hour tells you nothing. Build the grouped view at the same time as the formula, or the column just sits there.

Force the output type. Number for the raw extractions, Text for the buckets, Checkbox for the flags. A number field guessed as text sorts 1, 10, 11, 2 — and hour-of-day charts built on that look bizarre in a way that takes an hour to diagnose.

Three formula attributes deep is the ceiling. The nested four-way bucket above is one formula and perfectly fine. The limit is formula attributes referencing other formula attributes.

Final thoughts

day() and hour() are the least glamorous entries in Attio's date library — two functions that pull one number out of something you already had. But they're the only ones that answer questions about pattern rather than sequence, and pattern questions are the ones that change how a team is staffed and scheduled rather than how a single deal is handled.

Ship hour(setTimezone({Created at}, "")) on inbound leads this week and group by it. You'll either confirm that your coverage matches your demand, which is worth knowing, or you'll find the hours where leads land and sit — which is worth considerably more.

For the rest of the library — every math, logic, date, text, and history function with CRM use cases — see the complete guide to Attio formula attributes.

And if you'd rather have coverage analysis, routing rules, and billing-safe date logic designed and shipped for you, that's literally what we do. Get a free workspace audit or see the AI-native Attio sprint.