CODEWITHSUNDEEP
excel
NebulousReveal
NebulousReveal

Reputation: 572

Dates in Excel and Text

Suppose I have a data set of the following form:

    Begin Date          End Date
    04/12/2005         06/12/2009
    07/10/2006         08/10/2010
        .                  .
        .                  .

Note that the actual dates begin on cells A2 and B2 . I want to extract the year from these dates. When I write the following:

=right(b2,4)

I get something else like 9976 . How does Excel convert dates into text and is there a way to preserve the date format when extracting the year?

Upvotes: 0

Views: 45

Answers (1)

leekaiinthesky
leekaiinthesky

Reputation: 5593

Use =YEAR(B2) to extract the year.

Excel dates are stored as integers, the number of days since 1/1/1900. If you change the displayed format of that cell to "General", you can see the date in its integer format.

So 6/12/2009 = 39976, and the rightmost four digits are 9976. But the YEAR function will just get the year for you.

If you want to convert the date into a text format, use the TEXT function with a date format. For example, if you want YYYY-MM format: =TEXT(B2, "YYYY-MM").

Upvotes: 4

Related Questions