How to Use the MONTH and YEAR Functions in Excel

MONTH and YEAR Functions in Excel

1. Start with a date-based dataset

• Ensure your dataset includes a date column (for example, service date or transaction date)
• This date field will be used to extract time-based insights like year and month

2. Extract the year from a date

• Use the YEAR function to pull the year from each date
• This creates a new column where each row shows the corresponding year (e.g., 2025, 2026)
• Fill the formula down the entire dataset

This allows you to group and analyze data by year without modifying the original date column

3. Extract the month from a date

• Use the MONTH function to return the numeric month (1 through 12)
• Apply the formula across all rows

This enables grouping by month for trend analysis and seasonal reporting

4. Convert month numbers into readable month names (optional)

• Use the TEXT function if you want month names instead of numbers (e.g., January, May, December)
• This is helpful for dashboards, charts, and reports where readability matters

5. Aggregate data by year using conditional functions

• Use SUMIFS to calculate totals (such as revenue) by year
• Reference the extracted year column as your criteria range
• Copy the formula down for each year you want to analyze

This allows you to quickly build year-over-year comparisons

6. Count records by year for operational insights

• Use COUNTIF or COUNTIFS to count the number of records per year
• This can be used to track metrics like total jobs, service calls, or transactions

Example use cases:

• Total jobs completed per year
• Number of service calls per year

7. Combine month and year into a single label

• Use the CONCAT function to combine month and year into one field
• This creates labels like “May – 2025” or “12 – 2026”
• Useful for charts, tables, and grouped reporting

8. Build combined time fields inside one formula (optional)

• Instead of creating separate columns, you can combine YEAR and MONTH inside a single calculation
• This approach reduces the number of helper columns and keeps your model cleaner

9. Use the extracted fields for reporting and analysis

• Group data by year for annual performance tracking
• Group data by month to identify seasonal trends
• Combine both for detailed time-based reporting

Result

You now have a structured dataset that allows you to analyze performance by year, month, or both, enabling cleaner reporting, better trend analysis, and more flexible Excel models.