Skip to main content

Date and Time Periods

Date calculations in VisiData use Python's datetime module. After casting a column to date type (@), you can use .year, .month, .day, and other date attributes directly in expressions. For more complex operations, import datetime in .visidatarc.

Learning Focus

Always cast your date column to type @ before using date expressions — VisiData can not compute .year on a string. The cast converts "2025-01-15" into a Python date object.

Setup: Import in .visidatarc​

# ~/.visidatarc
from datetime import date, timedelta
from dateutil.relativedelta import relativedelta  # optional: for month arithmetic

Date Function Reference​

Excel / Google SheetsVisiData PythonDescription
=TODAY()= date.today()Current date
=NOW()= __import__('datetime').datetime.now()Current datetime
=YEAR(A2)= date_col.yearExtract year
=MONTH(A2)= date_col.monthExtract month (1–12)
=DAY(A2)= date_col.dayExtract day of month
=WEEKDAY(A2, 2)= date_col.weekday()Day of week (0=Mon, 6=Sun)
=WEEKNUM(A2)= date_col.isocalendar()[1]ISO week number
=DATEDIF(A2, B2, "d")= (date_b - date_a).daysDays between dates
=DATEDIF(A2, B2, "m")= (date_b.year - date_a.year) * 12 + (date_b.month - date_a.month)Months between dates
=DATEDIF(A2, B2, "y")= date_b.year - date_a.yearYears between dates
=EOMONTH(A2, 0)= date(d.year, d.month % 12 + 1, 1) - timedelta(days=1)End of current month
=EOMONTH(A2, -1)= date(d.year, d.month, 1) - timedelta(days=1)End of previous month
=DATE(year, month, day)= date(year_col, month_col, day_col)Build a date from parts
=NETWORKDAYS(A2, B2)Custom function in .visidatarcBusiness days between dates
=TEXT(A2, "YYYY-MM")= f"{d.year}-{d.month:02d}"Year-month string
=TEXT(A2, "MMM YYYY")= d.strftime('%b %Y')Month abbreviation + year
=TEXT(A2, "Q")= (d.month - 1) // 3 + 1Fiscal quarter (1–4)
=DAYS(B2, A2)= (date_b - date_a).daysSame as DATEDIF "d"
=EDATE(A2, 3)= date(d.year, d.month + 3, d.day)Add N months

Casting to Date Type​

Before using any date expression, cast the column:

vd ~/github/practice-folder/visidata/04-cleaning/01-dirty_orders.csv

# Cast 'order_date' to date type
# Move to 'order_date' column → press @
@

# VisiData auto-detects common formats:
# YYYY-MM-DD, DD/MM/YYYY, MM/DD/YYYY, etc.
# If auto-detection fails, set format in .visidatarc:
# options.disp_date_fmt = '%d/%m/%Y'

Date Extraction​

vd ~/github/practice-folder/visidata/04-cleaning/01-dirty_orders.csv

# After casting order_date to @...

# Extract year
=
# Enter: order_date.year
# Name: order_year

# Extract month number
=
# Enter: order_date.month
# Name: order_month

# Extract month name
=
# Enter: order_date.strftime('%B')
# Name: month_name

# Extract quarter
=
# Enter: (order_date.month - 1) // 3 + 1
# Name: quarter

Days Between Dates (DATEDIF)​

# Days since order date (aging analysis)
=
# Enter: (date.today() - order_date).days
# Name: days_since_order

# Days until deadline
=
# Enter: (deadline_date - date.today()).days
# Name: days_remaining

Year-Month Period Column​

# Create YYYY-MM period column for grouping
=
# Enter: f"{order_date.year}-{order_date.month:02d}"
# Name: period

# Now frequency table by period shows monthly counts
# Move to 'period' → Shift+F

Fiscal Period (Offset Calendar)​

For fiscal years starting in April (common in UK/Singapore accounting):

# Fiscal year: April = start of FY
=
# Enter: order_date.year if order_date.month >= 4 else order_date.year - 1
# Name: fiscal_year

# Fiscal quarter (FY starting April)
=
# Enter: ((order_date.month - 4) % 12) // 3 + 1
# Name: fiscal_quarter

Aging Analysis​

Classic accounts receivable aging buckets:

vd ~/github/practice-folder/visidata/04-cleaning/01-dirty_orders.csv

# Cast due_date to @
# Move to 'due_date' → @

# Calculate days overdue
=
# Enter: (date.today() - due_date).days
# Name: days_overdue

# Classify into aging buckets
=
# Enter: 'current' if days_overdue <= 0 else '1-30 days' if days_overdue <= 30 else '31-60 days' if days_overdue <= 60 else '61-90 days' if days_overdue <= 90 else '90+ days'
# Name: aging_bucket

# Frequency table of aging buckets
# Move to 'aging_bucket' → Shift+F

Business Days Calculation​

Add to .visidatarc for a business days function:

# ~/.visidatarc
from datetime import date, timedelta

def business_days_between(start, end):
    """Count business days (Mon-Fri) between two dates."""
    days = 0
    current = start
    while current < end:
        if current.weekday() < 5:  # Mon=0, Fri=4
            days += 1
        current += timedelta(days=1)
    return days

Then use in VisiData:

=
# Enter: business_days_between(order_date, delivery_date)
# Name: business_days

Date Comparison and Filtering​

vd ~/github/practice-folder/visidata/04-cleaning/01-dirty_orders.csv

# Filter orders from 2025 only
z|
# Enter: order_date.year == 2025

# Filter Q1 2025
z|
# Enter: order_date.year == 2025 and order_date.month in [1, 2, 3]

# Filter overdue orders
z|
# Enter: (date.today() - due_date).days > 30

Month-over-Month Comparison Pivot​

vd ~/github/practice-folder/visidata/04-cleaning/01-dirty_orders.csv

# After casting order_date to @...

# Create period column
=
# Enter: f"{order_date.year}-{order_date.month:02d}"
# Name: period

# Set amount aggregator
# Move to 'amount' → + sum

# Mark period as key column
# Move to 'period' → !

# Open frequency table for period totals
# Move to 'period' → Shift+F
# Shows: period, order count, total amount per month

Hands-On Practice​

vd ~/github/practice-folder/visidata/04-cleaning/01-dirty_orders.csv

# 1. Cast 'order_date' to date: move to column → @

# 2. Extract year:
=
# Enter: order_date.year
# Name: year

# 3. Extract quarter:
=
# Enter: (order_date.month - 1) // 3 + 1
# Name: quarter

# 4. Create period string:
=
# Enter: f"{order_date.year}-{order_date.month:02d}"
# Name: period

# 5. Days since order:
=
# Enter: (date.today() - order_date).days
# Name: age_days

# 6. Filter recent orders: z| → order_date.year == 2025
# 7. Open filtered: "
# 8. Press Ctrl+S → save as /tmp/orders_2025.csv

What's Next​