Notion Formulas 101: Auto-Populate From Dates (Weekday, Days Elapsed, Progress Bar, Etc.)
Based on Red Gregory's video on YouTube. If you like this content, support the original creators by watching, liking and subscribing to their content.
Use year(dateProp) and formatDate(dateProp, …) to auto-generate year, month, weekday, and custom date strings from a single date property.
Briefing
Notion formulas can automatically derive a wide range of date-based properties—years, months, weekdays, quarters, custom display strings, and even “days elapsed” and a progress bar—by combining built-in date functions with conditional logic and date-range math. The practical payoff is reducing manual data entry: once a record has a date (or start/end dates), related fields update instantly as those dates change.
The walkthrough starts with single-date extraction. A year property is generated with a simple year(date) pattern. For months, it highlights a common gotcha: Notion’s month numbering uses 0 for January through 11 for December, so a “month number” formula may need formatting to match human expectations. To avoid that mismatch, it uses formatDate(date, “MM”) to produce the correct month number, and it also demonstrates month name output via formatDate(date, “MMMM”) or abbreviations with shorter format strings.
Weekday and time are handled similarly. Weekday names come from formatDate(date, “dddd”) (with lowercase formatting tokens), while numeric weekdays can be produced by using a single “d” token—mapping Monday through Sunday to 1–6 with Sunday as 0. Time extraction uses formatDate(date, “HH:mm”) and then converts from 24-hour to 12-hour display by switching to the appropriate hour token and adding the AM/PM marker.
Beyond basic fields, the tutorial builds higher-level calendar logic. It creates a biannual label by checking the month number and returning “first” or “second” depending on whether the month is before or after June. It then computes quarters using chained if conditions based on month thresholds (e.g., month < 3 → quarter one, month < 6 → quarter two, and so on). It also shows how to format a custom date string like “Jun 29” or “Monday Jun 29” by combining month and day tokens in formatDate.
For “days elapsed,” it uses dateBetween(date, now) to count how many days remain or have passed relative to today, producing negative values when the deadline is in the past. The same approach scales to date ranges by adding an end date and using dateBetween(start, end) to measure span length.
The most advanced section turns range timing into a progress indicator. It calculates the percentage of the current time window that has elapsed by dividing dateBetween(now, start) by dateBetween(end, start), then rounds the result and formats it as a percent. To keep formulas from breaking when the record is outside the active range, it returns an empty value for false conditions and uses number() casting to ensure empty branches behave like numbers. Finally, it converts the percent into a visual progress bar using concat and a slice-based emoji approach: the formula “slices” a 100% bar from left and right based on the elapsed fraction, then appends the rounded percent text.
Overall, the workflow is a toolkit: date functions for extraction, if statements for categorization, dateBetween for elapsed-time math, and concat/slice for presentation—together enabling fully auto-populated Notion properties that stay accurate as dates change.
Cornell Notes
The core idea is to use Notion’s date functions to auto-populate properties from a single date or a start/end date range. The workflow extracts year, month, weekday, time, and custom formatted strings using year(), month(), and formatDate() with the correct token casing (Notion’s month index starts at 0). It then layers in logic with if statements to label biannual periods and compute quarters, and it measures elapsed time with dateBetween(). For ranges, it calculates a percent-complete value relative to now and turns that percent into an emoji progress bar using concat and slice, while guarding against out-of-range records with conditional empty outputs and type casting.
Why does month extraction sometimes look “off” in Notion, and how can formulas correct it?
How can a formula produce weekday names vs weekday numbers?
What’s the pattern for deriving quarters and biannual labels from a date?
How does dateBetween() help compute “days elapsed” relative to today?
How is percent-complete calculated for a date range, and how is it prevented from showing outside the active window?
How does the progress bar turn a percent into an emoji visualization?
Review Questions
- When using month(date) in Notion, what index does January map to, and how would that affect a quarter formula?
- What does dateBetween(dateProp, now) return when the dateProp is one day in the past?
- Why might number() be needed in a conditional date-range percent formula, and what problem does it prevent?
Key Points
- 1
Use year(dateProp) and formatDate(dateProp, …) to auto-generate year, month, weekday, and custom date strings from a single date property.
- 2
Account for Notion’s zero-based month indexing (January = 0) when using month(date) for logic; prefer formatDate(date, “MM”) when you need human month numbers.
- 3
Derive weekday names and abbreviations with formatDate using the correct lowercase “d” tokens, and use numeric weekday output with a single “d” token.
- 4
Compute quarters and biannual labels with if statements driven by month(date) thresholds, returning consistent text outputs for each branch.
- 5
Calculate days elapsed or countdowns with dateBetween(dateProp, now), which yields negative values for past dates.
- 6
For date ranges, measure span length with dateBetween(start, end) and compute percent-complete as dateBetween(now, start) / dateBetween(end, start).
- 7
Build a progress bar by converting the percent into a slice-based emoji display using concat, while guarding out-of-range records with conditional empty outputs and type casting (e.g., number()).