Function Problems

Automatically Update the Current Year in an Excel Date Formula

Rana GarciaRana Garcia Nov 18, 2026 868 views

Question details

Compare the date in A2 with January 1 of the current year using a formula that updates automatically.

Automatically Update the Current Year in an Excel Date Formula
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet needs to label dates as Active or Inactive without a hard-coded year.
Observed behavior
The comparison uses January 1 of the current year and updates when the worksheet recalculates after the system date changes.
Before you start

Check that A2 contains a valid Excel date rather than text, and verify that your device's system date is correct.

Solution 1Recommended

Compare A2 with January 1 of the current year

Use TODAY, YEAR, and DATE to create a comparison date that changes with the year.

1
Enter the date

Enter a valid date in cell A2 or select the cell that already contains the date to compare.

2
Add the formula

Select an empty cell, enter =IF(A2>=DATE(YEAR(TODAY()),1,1),"Active","Inactive"), and press Enter.

3
Check recalculation

In Excel, open Formulas > Calculation Options and select Automatic so the result updates when the workbook recalculates.

Compare A2 with January 1 of the current year
Future dates also count as Active: The formula returns Active for any date on or after January 1 of the current year, including dates in future years.
WPS Spreadsheets

Update the year automatically in WPS Spreadsheets

Use the same TODAY, YEAR, and DATE formula in WPS Spreadsheets to compare a date with the start of the current year.

  1. 1. Open your worksheet: Open WPS Spreadsheets and select the workbook containing your dates.
  2. 2. Check the date: Enter or verify a valid date in cell A2.
  3. 3. Enter the formula: Select an empty cell, type =IF(A2>=DATE(YEAR(TODAY()),1,1),"Active","Inactive"), and press Enter.
  4. 4. Recalculate if needed: If the result has not updated after the system date changes, press F9 to recalculate the worksheet.
Create a January 1 comparison date without hard-coding the yearOpen and edit Microsoft Excel XLSX filesUse familiar spreadsheet formulas and cell references
microsoft office alternative - wps office

Frequently Asked Questions

How does the formula find January 1 of the current year?

TODAY() reads the current system date, YEAR(TODAY()) extracts its year, and DATE(YEAR(TODAY()),1,1) returns January 1 of that year.

Why hasn't the result changed after January 1?

Check your device's system date, then recalculate the workbook. In Excel, select Formulas > Calculation Options > Automatic to enable automatic recalculation.

How can I mark only dates in the current year as Active?

Use =IF(AND(A2>=DATE(YEAR(TODAY()),1,1),A2<DATE(YEAR(TODAY())+1,1,1)),"Active","Inactive"). This excludes dates in future years.

What if the date in A2 is stored as text?

Convert it to a valid spreadsheet date before comparing it. In Excel, select A2 and use Data > Text to Columns, then choose the appropriate date format in the final step.