Office & Productivity Software forum

General discussion

Excel Formula

by htmlbrain / October 18, 2007 4:49 AM PDT

I haven't been able to find a way to display the year in Excel. (Its office 2003)

I want it to display just the year, not any other thing (such as the month or day)

Any help would be appreciated

Discussion is locked
You are posting a reply to: Excel Formula
The posting of advertisements, profanity, or personal attacks is prohibited. Please refer to our CNET Forums policies for details. All submitted content is subject to our Terms of Use.
Track this discussion and email me when there are updates

If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.

You are reporting the following post: Excel Formula
This post has been flagged and will be reviewed by our staff. Thank you for helping us maintain CNET's great community.
Sorry, there was a problem flagging this post. Please try again now or at a later time.
If you believe this post is offensive or violates the CNET Forums' Usage policies, you can report it below (this will not automatically remove the post). Once reported, our moderators will be notified and the post will be reviewed.
Collapse -
by MarkFlax Forum moderator / October 18, 2007 6:02 AM PDT
In reply to: Excel Formula

If a date is entered into A2, eg 10/17/2007, then in A3 type =YEAR(A2)

However, the date in A2 must be entered in DATE format, ie use =DATE(2007,10,17)

This will display 2007 in A3.

Awkward but can be used. I don't know if others have easier solutions.

I hope that helps


Collapse -
Thanks alot
by htmlbrain / October 18, 2007 6:15 AM PDT
In reply to: YEAR(A2)

Thanks alot Mark - thats really helpful!
It worked, which is a bit of a relief after days of trying to make it work


Collapse -
"days of trying"
by Kees Bakker / October 18, 2007 9:06 PM PDT
In reply to: Thanks alot

A few posts below, I post 2 simple solutions. Just one formula, one of them combines with a custom formatting. That's basic use of Excel. Nothing difficult or advanced.

Sometimes, it pays off to invest time in study, education, books and courses. I'm sure working through a 3-days Excel course would have learned you not only this, but quite a lot of useful other things, so it would save you a tremendous amount of time in the future.

Just a suggestion.


Collapse -
Re: date format
by Kees Bakker / October 18, 2007 8:05 AM PDT
In reply to: Excel Formula

Sorry, your requirements are far from clear. To display 2007, just enter 2007. It's uncommon (to say the least) to enter 11/23/2007 (American date format) in a cell and wanting to see only the 2007 part. It's easily possible (by using the date format yyyy, in stead of the more usual m/d/yy or mm/dd/yyyy), but uncommon.

So I assume you want to enter a date in a cell (like B1) and show the year in another cell (like C1). Then indeed, the formula is =year(B1). The way you enter the date in B1 isn't important at all, as long as Excel understands or knows it's a date. De DATE-formula used by Mark is rather clumsy. Just entering it as 11/23/2007 works just as well.

Hope this helps.


Collapse -
by htmlbrain / October 18, 2007 4:42 PM PDT
In reply to: Re: date format

Sorry if its unclear - but what i meant was i needed to display the current year (so it would be 2007 if you checked this year and 2008 if you checked it next year). I can display the current date now, and frm that i can get the current year now Happy

Collapse -
The two possibilities for that:
by Kees Bakker / October 18, 2007 6:42 PM PDT
In reply to: Ooops

1. =year(now()) - with nested function
2. =now, with the field formatted as yyyy

Your choice.


Collapse -
Now that's weird.
by MarkFlax Forum moderator / October 18, 2007 6:42 PM PDT
In reply to: Re: date format

I use Excel 2002, (XP).

When I tried this out, eg a date in European format such as 17/10/2007 in A1, and entered =Year(A1) in A2, all I got was a cell with numbers, (possibly Julian format?).

That confused me as I am sure I've used Year(cellref) before. When I checked Help it told me that I had to use =Date(yy,dd,mm) format for this to work.

However, I have just tried it again (all that was yesterday and now is today), and it worked as normal.

I knew I had done this before!

It must have been me! Happy


Popular Forums
Computer Newbies 10,686 discussions
Computer Help 54,365 discussions
Laptops 21,181 discussions
Networking & Wireless 16,313 discussions
Phones 17,137 discussions
Security 31,287 discussions
TVs & Home Theaters 22,101 discussions
Windows 7 8,164 discussions
Windows 10 2,657 discussions

The Samsung RF23M8090SG

One of the best French door fridges we've tested

A good-looking fridge with useful features like an auto-filling water pitcher and a temperature-adjustable "FlexZone" drawer. It was a near-flawless performer in our cooling tests.