Skip to main content

Working with Date and Time

Extract and manipulate date components with TODAY, NOW, YEAR, MONTH, DAY, and other date/time formulas.

Relevant Formulas​

TODAY​

Returns today's date.

View TODAY Formula Reference →

NOW​

Returns the current date and time.

View NOW Formula Reference →

YEAR​

Returns the year component of a date.

View YEAR Formula Reference →

MONTH​

Returns the month component of a date.

View MONTH Formula Reference →

DAY​

Returns the day component of a date.

View DAY Formula Reference →

Additional Date/Time Formulas​

Basic Usage​

Current Date/Time​

=TODAY()
Result: 2024-03-15

=NOW()
Result: 2024-03-15 14:30:00

Extracting Components​

=YEAR([order_date])
Input: 2024-03-15
Result: 2024

=MONTH([order_date])
Result: 3

=DAY([order_date])
Result: 15

Common Use Cases​

Age Calculation​

// Age in years
=YEAR(TODAY()) - YEAR([birth_date])

// More accurate age
=DAYS(TODAY(), [birth_date]) / 365.25

Days Since/Until​

// Days since order
=DAYS(TODAY(), [order_date])

// Days until deadline
=DAYS([deadline], TODAY())

Date Categorization​

// Categorize by year
=YEAR([date])

// Quarter
=IF(MONTH([date]) <= 3, "Q1",
    IF(MONTH([date]) <= 6, "Q2",
        IF(MONTH([date]) <= 9, "Q3", "Q4")))

Date Formatting​

// Month-Year display
=CONCAT(MONTH([date]), "-", YEAR([date]))

// Custom date format
=CONCAT(YEAR([date]), "-", MONTH([date]), "-", DAY([date]))

Advanced Techniques​

Relative Dates​

// 30 days ago
=TODAY() - 30

// First of current month
=DATE(YEAR(TODAY()), MONTH(TODAY()), 1)

Date Comparisons​

// Check if recent
=IF(DAYS(TODAY(), [date]) <= 30, "Recent", "Old")

// Check if future
=IF([date] > TODAY(), "Future", "Past")

Business Date Calculations​

// Is weekend
=IF(OR(WEEKDAY([date]) = 1, WEEKDAY([date]) = 7), "Weekend", "Weekday")

Common Patterns​

Fiscal Year​

// Fiscal year (July start)
=IF(MONTH([date]) >= 7,
    YEAR([date]),
    YEAR([date]) - 1)

Season Determination​

=IF(MONTH([date]) IN (12, 1, 2), "Winter",
    IF(MONTH([date]) IN (3, 4, 5), "Spring",
        IF(MONTH([date]) IN (6, 7, 8), "Summer", "Fall")))

Expiration Checks​

// Is expired
=IF([expiry_date] < TODAY(), "Expired", "Valid")

// Days until expiration
=DAYS([expiry_date], TODAY())

Best Practices​

  1. Use TODAY() for Current Date - Automatically updates
  2. Extract Components for Grouping - YEAR, MONTH for aggregations
  3. Handle NULL Dates - Use COALESCE or ISBLANK
  4. Consider Timezones - Be aware of timezone in NOW()
  5. Validate Date Ranges - Check for reasonable values

Additional Formula References​

Additional examples will be added soon.