Automatically Update the Current Year in an Excel Date Formula
Question details
Compare the date in A2 with January 1 of the current year using a formula that updates automatically.

- 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.
Check that A2 contains a valid Excel date rather than text, and verify that your device's system date is correct.
Compare A2 with January 1 of the current year
Use TODAY, YEAR, and DATE to create a comparison date that changes with the year.
Enter a valid date in cell A2 or select the cell that already contains the date to compare.
Select an empty cell, enter =IF(A2>=DATE(YEAR(TODAY()),1,1),"Active","Inactive"), and press Enter.
In Excel, open Formulas > Calculation Options and select Automatic so the result updates when the workbook recalculates.

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. Open your worksheet: Open WPS Spreadsheets and select the workbook containing your dates.
- 2. Check the date: Enter or verify a valid date in cell A2.
- 3. Enter the formula: Select an empty cell, type =IF(A2>=DATE(YEAR(TODAY()),1,1),"Active","Inactive"), and press Enter.
- 4. Recalculate if needed: If the result has not updated after the system date changes, press F9 to recalculate the worksheet.

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.




