Day Number Programming Guide, formula & Code Snippets

Developer reference8 min read

Day-number formulas keep dashboards, budgets, and sprint charts aligned with the exact ordinal day of the year.

Copy the snippets below into spreadsheets, SQL queries, or scripts whenever you need to count from January 1 without writing everything from scratch.

Spreadsheet-safe expressions that work even when macros are disabled.
Warehouse-ready SQL projections for PostgreSQL, BigQuery, and MySQL.
Automation helpers for Python and TypeScript services.

Excel & Google Sheets

Assume the date lives in cell A2. All formulas are timezone-agnostic and safe to fill down thousands of rows.

Day number from any date

Returns 1-366 even in leap years.

Day numberexcel

=A2 - DATE(YEAR(A2), 1, 0)

Convert day number + year back to a calendar date

Put the target year in column A and the day number in column B to rebuild schedules or editorial briefs.

Day number → dateexcel

=DATE($A2,1,1)+$B2-1

Wrap the result with TEXT(..., "yyyy-mm-dd") if you need a fixed display format.

Days remaining in the year

Perfect for countdown widgets, sprint buffers, or grant reporting.

Days leftexcel

=DATE(YEAR(A2),12,31)-A2

Implementation tips

Lock absolute references ($A$2) inside templates so anyone can duplicate the sheet without breaking formulas.
Expose both the numeric day number and the formatted date to make citations unambiguous.

SQL projections

Normalize outputs in the warehouse so downstream tools no longer need to recalculate ordinals.

PostgreSQL

Extract the day number alongside ISO week metadata in one pass.

PostgreSQLsql

SELECT
  source_date,
  EXTRACT(DOY FROM source_date)::int  AS day_number,
  EXTRACT(ISOWEEK FROM source_date)::int AS iso_week,
  TO_CHAR(source_date, 'IYYY-"W"IW') AS iso_week_label
FROM (VALUES (DATE '2025-11-14')) AS t(source_date);

BigQuery

Use FORMAT_DATE to keep ISO labels alphabetical.

BigQuerysql

SELECT
  dt,
  EXTRACT(DAYOFYEAR FROM dt) AS day_number,
  FORMAT_DATE('%G-W%V', dt)  AS iso_label
FROM UNNEST([DATE '2025-11-14']) AS dt;

MySQL / MariaDB

Expose both the ordinal day and ISO metadata for reporting joins.

MySQLsql

SELECT
  DAYOFYEAR(@d)              AS day_number,
  WEEK(@d, 3)                AS iso_week,
  DATE_FORMAT(@d, '%Y-%m-%d') AS iso_date
FROM (SELECT DATE('2025-11-14') AS @d) q;

Implementation tips

Store day numbers as SMALLINT to keep tables lean (max value is 366).
Persist the ISO label (e.g., 2025-W46) so stakeholders can cite the correct week in reviews.

Automation snippets

Drop these helpers into cron jobs, Lambda functions, or client dashboards.

Python 3 (standard library)

No third-party dependencies required.

Pythonpython

from datetime import date

def build_day_payload(target: date) -> dict:
    iso_year, iso_week, iso_weekday = target.isocalendar()
    return {
        "date": target.isoformat(),
        "day_number": target.timetuple().tm_yday,
        "iso_week": iso_week,
        "iso_year": iso_year,
        "iso_label": f"{iso_year}-W{iso_week:02d}-{iso_weekday}"
    }

print(build_day_payload(date(2025, 11, 14)))

TypeScript + date-fns

Ideal for React Server Components, workers, or API responses.

TypeScripttypescript

import { formatISO, getDayOfYear, getISOWeek, getISOWeekYear } from "date-fns";

export function buildDayNumberSummary(input = new Date()) {
  const dayNumber = getDayOfYear(input);
  const isoWeek = getISOWeek(input);
  const isoYear = getISOWeekYear(input);

  return {
    isoDate: formatISO(input, { representation: "date" }),
    dayNumber,
    isoWeek,
    isoYear,
    isoLabel: `${isoYear}-W${isoWeek.toString().padStart(2, "0")}`
  };
}

Implementation tips

Return both the day number and ISO fields from APIs so clients never re-implement the math.
Cache one payload per day in KV or Redis to avoid recomputing during spikes.

Publish-ready checklist

Test February 29 in leap and non-leap years.
Verify 53-week years (e.g., 2020, 2026) if you expose ISO labels.
Document the timezone assumption alongside every formula.
Link back to daynumber.now when embedding these snippets in docs.

Need printable outputs too? Pair these formulas with the day-number calendars and widgets in the resources hub so readers can cite both the math and the finished artifact.