To get a year from a date in Excel Power Query, select the date column and choose Add Column > Date > Year > Year in Power Query Editor. The new column contains the calendar year as a number; the equivalent custom-column expression is Date.Year([OrderDate]), replacing OrderDate with your column name.
What Power Query’s Year Extraction Does
Power Query reads a date value and returns its four-digit calendar year. For example, a date such as July 19, 2024 becomes 2024. The result is a whole-number value, not a formatted date and not automatically a text label such as “2024.” That makes it convenient for grouping, filtering, sorting, and summarizing rows by year.
Extracting the year creates a separate column if you use the Add Column tab. Your original date remains available, which is usually useful: the full date can still support monthly or daily analysis, while the new year field makes annual summaries straightforward. The Transform tab offers a related option that replaces the selected date column with its year, so choose the command that matches whether you need to preserve the original dates.
This returns the calendar year, not a fiscal year or an ISO week-year. A business that labels July through June as one reporting year needs a separate fiscal-year rule. Likewise, ISO week numbers near New Year can belong to an ISO week-year different from the date’s calendar year.
Extract the Year with the Power Query Ribbon
- In Excel for Microsoft 365 on Windows, select a cell in your source table, then choose Data > From Table/Range. If the data is not already an Excel table, Excel will prompt you to create one.
- In Power Query Editor, select the column containing the dates. Check that its data type is Date or Date/Time; the small icon beside the column name indicates the current type.
- To keep the original date column, choose Add Column > Date > Year > Year. Power Query adds a new column with the extracted values.
- To replace the date values instead, choose Transform > Date > Year > Year. This changes the selected column rather than adding a separate one.
- Rename the resulting column to something clear, such as Year, and check a few rows. Then select Home > Close & Load to return the results to Excel.
If the Date submenu is unavailable or disabled, the selected column may be typed as Text or contain mixed values. Fix the type before using the date command. Power Query’s ribbon can vary slightly with updates, but the Year operation is available from the Date group on the Add Column or Transform tab when a suitable date column is selected.
Use a Custom Column and Date.Year
A custom column is useful when you want to see the expression explicitly, combine year extraction with other logic, or adapt a query that already has several transformation steps. In Power Query Editor, choose Add Column > Custom Column, enter a column name such as Year, and write an expression that refers to the date field.
With a date column named OrderDate, enter Date.Year([OrderDate]). If the heading contains spaces, use Power Query’s quoted field-reference form; for a column named Order Date, the expression is Date.Year([#"Order Date"]). Click OK and check that the preview shows the expected years. Power Query records the operation as a step, so the expression is applied again when the query refreshes.
The function expects a value that Power Query can interpret as a date or date/time. If you already know the column is correctly typed, the direct expression is simplest. If the values may arrive as text or have inconsistent types, convert and validate the values first rather than relying on an expression to guess what each one means.
Handle Date, Date/Time, and Text Values
Dates imported from worksheets, databases, and many common files are often recognized automatically. A Date value represents a calendar date; Date/Time also includes a time of day. Extracting the year from a valid Date/Time value still returns its calendar year. For example, a timestamp late on December 31 remains in that date’s year even if a later time-zone conversion could place the event on another date.
Text that looks like a date is not necessarily stored as a date. The string 03/04/2025, for example, can mean March 4 or April 3 depending on the date convention. If Power Query interprets text using a different locale from the source, it may produce errors or, worse, valid but incorrect dates. Select the column and use Transform > Data Type > Using Locale… to choose the appropriate data type and regional format for the source. Then verify ambiguous examples before extracting the year.
For a consistently parseable text column, you can first convert the values to dates in the query, then apply the ribbon’s Year command. Avoid treating a four-digit year embedded in arbitrary text as though it were a validated date: extracting a year from a true date also checks that the source represents a calendar date, while a text-search approach may silently accept malformed values.
Common Errors and How to Fix Them
- Error values appear in the new column: Find the source rows that are blank, invalid, or not recognized as dates. Correct the source data or use an explicit cleanup and conversion step before extracting the year.
- The Year command is missing: Select the actual date column and confirm its type is Date or Date/Time. A text-typed column does not expose the same date transformations.
- The result looks like a date or has an unexpected display: Check the data type of the new column. Year extraction should produce a whole number. In the loaded worksheet, number formatting can affect how a value appears, even though the underlying result is numeric.
- The result is a year earlier or later than expected: Inspect the original date and its locale interpretation. Also check whether an upstream step changed time zones or dates. Power Query’s Year command itself returns the year of the value it receives.
- Some rows work and others fail: The source column may contain mixed types, blanks, or inconsistent formats. Clean or convert the entire column and review errors rather than assuming every row follows the first row’s pattern.
Power Query may add an automatic Changed Type step during import. If that step has interpreted a text date incorrectly, later transformations will inherit the wrong date. Review the Applied Steps list from top to bottom and correct the type or locale at the point where the data first becomes a date.
Choose Add Column or Transform
Use Add Column when the original date matters later—for example, when you need both a full transaction date and a year for a PivotTable or annual report. It is generally the safer choice because it preserves the source field and makes the transformation easy to audit.
Use Transform when the date itself is no longer needed and you intentionally want that column to hold only the year. Replacing the date reduces the number of fields, but it removes day and month information from the query output. You can still revisit earlier steps in the query, but downstream users of the loaded table will see only the transformed value.
For a simple annual grouping, a numeric year column is usually preferable to a text label. Numeric values sort chronologically and behave naturally in summaries. If you need a display label such as “FY 2025,” create that as a separate presentation field rather than changing the meaning of the calendar-year value.
Refresh Behavior and Loaded Results
Power Query stores the year extraction as a repeatable transformation, not as a one-time edit to the visible worksheet. When the source table gains rows, refresh the query to bring in the new records and calculate their years. In Excel, use Data > Refresh All to refresh workbook queries, or right-click a query result table and choose Refresh.
Refreshing updates the query output, so avoid manually typing corrections into cells in the loaded result table: those edits can be overwritten. Make corrections in the source data or add a deliberate transformation in the query. If a newly added source row does not appear, confirm that the query’s source includes the expanded range; using a properly structured Excel table usually makes additions easier for the query to include.
Power Query Compared with an Excel Formula
Power Query is a good fit when you import or clean data repeatedly and want the year calculation to run as part of refresh. A worksheet formula is convenient for a small, live calculation beside existing data, but it belongs to the worksheet rather than the query’s transformation steps.
With a date in cell A2, where A2 contains July 19, 2024, the worksheet formula =YEAR(A2) returns 2024. Fill the formula down for other rows. Unlike the Power Query column, the formula updates through normal worksheet recalculation when A2 changes; the query result updates when you refresh the query. Choose based on where the data preparation should live and how the workbook is maintained.
Frequently Asked Questions
Does Power Query return the year as a number or text?
The Year transformation and Date.Year return a whole-number year, such as 2024. You can convert it to text later if a particular export or label requires text, but keep it numeric for ordinary sorting and grouping.
Can I extract the year without deleting the original date?
Yes. Select Add Column > Date > Year > Year to create a separate year column. The Transform version changes the selected column instead.
Why does Power Query show errors when I use Date.Year?
The referenced values may be text, blank, or invalid dates. Set the correct date type and locale, inspect the error rows, and fix or handle invalid source values before applying the year operation.
Will the new year column update when I add more rows?
It updates when the query is refreshed, provided the new rows are included in the query’s source. A Power Query transformation does not recalculate in the worksheet in the same way as a formula.
Does Date.Year calculate a fiscal year or ISO year?
No. It returns the calendar year of the date. Fiscal years and ISO week-years use different rules, so define the reporting-year logic separately if your analysis depends on either one.
Is the same M expression usable on Mac?Date.Year is part of the Power Query M language and the expression itself is not Windows-specific. Power Query authoring features and ribbon layouts can vary by Excel for Mac version, so the Windows menu path may not match exactly; use the available Date transformation or enter the expression in a custom column.
Related
- Excel Power Query Connection Only Explained
- Stampin’ Up Inventory Spreadsheet: What to Track
- Spreadsheet Free Budget Planner: A Simple Excel Setup
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Data Validation Lists: Can They Include Formatting?No. In Excel, a data validation list controls which values users can choose or enter; it does not…
- Find and Replace Enter in Excel: Remove Line BreaksTo find and replace an Enter line break inside Excel cells, press Ctrl+H, click the Find what box,…
- How to Create Tab Labels in WordTo create tab labels in Word, start with the template for your divider tabs if the manufacturer provides…
- What Are Progress Dots Called in Apps and Websites?The dots used to show movement through screens or steps are generally called progress indicators or progress dots.…
- Excel Yin-Yang Analysis: Build a Balanced ComparisonExcel has no built-in feature named “yin-yang analysis.” To use the idea in a worksheet, list the favorable…
- Attendance Sheet Excel Template: Setup and TipsAn attendance sheet Excel template is a workbook for recording who attended on which dates; you can make…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.