What Year Is My Excel

This is your time to shine. The Microsoft Excel YEAR function returns a four-digit year (a number from 1900 to 9999) given a date value. The YEAR function is a built-in function in Excel that is categorized as a Date/Time Function. The following years are leap years: 1600, 2000, 2400. This is because they are evenly divisible by both 100 and 400. Because versions of Microsoft Excel earlier than Excel 97 handle only years from 1900 to 2078, only the year 1900 is subject to the 100/400 exclusion rule of leap years in Microsoft Excel.

How to extract or get the year, month and day from date list in Excel?

The MyExcelOnline Academy is an online course & platform for people who want to upgrade their Excel skills, level and office efficiency. There's a library of 500+ video tutorials (each just 3 minutes long) plus online support from our team of Microsoft Certified Trainers. Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900.

For a date list, do you know how to extract or get the year, month and day number? See screenshot below. In this article, we will show you formulas of getting year, month and day number separately from a date list in Excel.

Extract the year number
Extract the month number
Extract the day number

Extract/get the year, month and day from date list in Excel

Take below date list as an example, if you want to get the year, month and day numbers from this list, please do as follows step by step.

Extract the year number

1. Select a blank cell for locating the year number, such as cell B2.

2. Copy and paste formula =YEAR(A2) into the Formula Bar, then press Enter key. Drag the Fill Handle down to the range you need to get all year number from the list.

Extract the month number

This section shows you the formula of getting the month number from the list.

1. Select a blank cell, copy and paste formula =MONTH(A2) into the Formula Bar and press the Enter key.

2. Drag the Fill Handle down to the range you need.

Then you will get the month numbers of the date list.

Extract the day number

The getting day number formula is as simple as the formulas above. Please do as follows.

Copy and paste formula =DAY(A2) into a blank cell D2 and press Enter key. Then drag the Fill Handle down to the range to extract all day numbers from the referenced date list.

Now the year, month and day numbers are extracted from the date list as above screenshot shown.

• Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails..
• Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range..
• Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns.. Prevent Duplicate Cells; Compare Ranges..
• Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select..
• Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more..
• Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments..
• Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic..
• Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF..
• More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

• Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
• Open and create multiple documents in new tabs of the same window, rather than in new windows.
• Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

• To post as a guest, your comment is unpublished.
  this website help me to find what ive been searching all this time. many thanks.
• To post as a guest, your comment is unpublished.
  This is stupid. Anyone who comes to this website wants the day of the month to populate into a cell! Anyone can plug in a random number into cell A2 and the click into cell B2 and insert the formula =A2. That's not what anyone is looking for that comes to this page. People want the cell to populate with the DAY OF THE MONTH!!!! This information and website is USELESS!!!

  • To post as a guest, your comment is unpublished.
    You wanted the actual day of the week or month?? Day of the week would be..
    =TEXT(B2,'dddd') for full day
    =TEXT(B704,'ddd') for 3 letter day like Mon, Tue, Wed etc
    
  • To post as a guest, your comment is unpublished.
    Did you actually read the site? The formula =DAY(A2) would extract the day of the month from whatever date you had entered in cell A2. How is that not what you're asking for?
    

    • To post as a guest, your comment is unpublished.
      No, that would extract the day number, not the actual day. I believe frank means he wants to see MON, TUE, WED etc.
      
• To post as a guest, your comment is unpublished.
  =MONTH(A2) drops the leading zero for single digit months. Having that cell formatted as Text does not solve. How to keep the leading zero?

  • To post as a guest, your comment is unpublished.
    =text(A2, 'mmm') for month name in abbreviations
    =text(A2, 'mmmm') for full month name

    • To post as a guest, your comment is unpublished.
      Thank u Sonu, u answered my question as well.

This Excel tutorial explains how to use the Excel YEAR function with syntax and examples.

Description

The Microsoft Excel YEAR function returns a four-digit year (a number from 1900 to 9999) given a date value.

The YEAR function is a built-in function in Excel that is categorized as a Date/Time Function. It can be used as a worksheet function (WS) and a VBA function (VBA) in Excel. As a worksheet function, the YEAR function can be entered as part of a formula in a cell of a worksheet. As a VBA function, you can use this function in macro code that is entered through the Microsoft Visual Basic Editor.

Syntax

The syntax for the YEAR function in Microsoft Excel is:

Parameters or Arguments

date_value

A valid date to return the year value.

Returns

Latest cod ps4. The YEAR function returns a numeric value between 1900 and 9999.

Applies To

• Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Function

• Worksheet function (WS)
• VBA function (VBA)

Example (as Worksheet Function)

Let's look at some Excel YEAR function examples and explore how to use the YEAR function as a worksheet function in Microsoft Excel:

Based on the Excel spreadsheet above, the following YEAR examples would return:

Example (as VBA Function)

The YEAR function can also be used in VBA code in Microsoft Excel.

What Year Is My Excel Program

Let's look at some Excel YEAR function examples and explore how to use the YEAR function in Excel VBA code:

If Cell Is Populated Then

In this example, the variable called LYear would now contain the value of 2001.