How to Extract Year, Month, Day & Weekday From Any Date in Excel (Automatically)

Dates look simple… Until you need to pull them apart. Maybe you want to group sales by year, filter by month, or check which weekday you had the most meetings on.

Good news: Excel can split any date into year, month, day, and even the weekday name for you, fully automatically.

This is one of those skills that instantly makes your spreadsheets cleaner, smarter, and easier to analyse.

1. Extract the Year: =YEAR( )

If your date is in cell A2, then use: =YEAR(A2). This will return a four digit year, for example 2025. This can be useful for creating annual reports, sorting invoices or tasks by year, or breaking down data.

2. Extract the Month Number: =MONTH( )

If your date is in cell A2, then extract the month using: =MONTH(A2). This will give you a month number between 1 and 12. This is great for a monthly sales dashboard, seasonality analysis, grouping data by month in pivot tables.

3. Extract the Month Name: =TEXT()

If you want the month name instead of number, you can use the =TEXT( ) formula. You can extract the full name (for example "January" or the short version ("Jan").

If the date is in A2, then you can extract the full name of the month using: =TEXT(A2;"mmmm").

If you only want the short version, you use the formula: =TEXT(A2;"mmm"). The difference between the two is the number of m's in the formula.

Note: Excel settings can vary from region to region, if ; doesn't work inside formulas, then try , instead.

4. Extract the Day: =DAY( )

If your date is in cell A2, then you can extract the day using: =DAY(A2). This will give you a date between 1 and 31. This can be super useful for tracking deadlines, sorting events, and cleaning messy date-time formats.

5. Get the Weekday Name: =TEXT( )

To pull the weekday name, for example "Monday" you can use the same logic as for pulling the month name instead of number. Assuming the date is still in A2, then to extract the full weekday name use: =TEXT(A2;"dddd").

Similarly, in order to extract the short version of the date use: TEXT(A2;"ddd"). For example, this will return "Mon".

Extracting the weekday name can be useful for planning schedules, analysing attendance or performance patterns, or identifying trends, for example if you want to know which day of the week you sell the most tickets.

Note: Excel settings can vary from region to region, if ; doesn't work inside formulas, then try , instead.

Common Issues & How to Fix Them

It is always important to be aware of formatting, because this can lead to Excel not being able to read the date or returning what looks to be a random number.

Excel Returns Random Number instead of a Date

If Excel returns a random number, then the date is formatted as a serial number. Don't panic, Excel stores dates as numbers which is what makes these formulas work. To fix this go to Home > Number Format > Short Date or Long Date.

Excel Returns an Error (for example #Value!)

If Excel returns an error message, this is because the date you are trying to extract from (A2) is formatted incorrectly. You then need to do a quick cleanup which can be done in two ways. First try Ctrl + 1 > Date Format. If it still doesn't work, then try: Data > Text to Columns. Now Excel should be able to read the date and return the information you want to extract.

Want more Excel Confidence?

We're building a Duolingo-style app that teaches Excel the fun way. If you want to be the first to try it (and grab our free Excel shortcut cheat sheet), join the waitlist here.