year function

R

Ricardo2

New Member
Joined
Apr 8, 2002
Messages
14
I enter a current year in a cell (i.e. 2002 in A1) then use the Year function in the next cell (B1). The result is 1905. I know that Excel uses a serial number to refer to dates so I copied the value to another cell to see and it was something like 1895. I remember reading that current dates are 5 digit serial numbers. I enter the year in cell A1 as a 4 digit number and not text.

I'm preparing a spreadsheet that will perform date calculations based on the year entered. How can I fix this? Am I missing something?

Thanks in advance for your help.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
Allow the datevalue for Jan 1st of each year to represent that year. Enter your years as...

1/2002

...and format as YYYY.
This message was edited by Mark W. on 2002-06-07 07:44
 
Upvote 0
Excel stores time and dates as fractions of a day. For most excel users (including you) 1 = January 1, 1900. So day 2002, is actually June 24,1905, thus your result. Today is 37,414. The year function is looking for a date. You can coerce your year into a real date, by using something like =YEAR("01-01-"&A1). You may also want to look at the date function. If this will be used throughout your speadsheet, I'd suggest coverting to a real date once, and tie all calculations to that cell. Good luck
 
Upvote 0
Hmmm, =YEAR("01-01-"&A1), doesn't that take you back to where you started? :smile:

BTW, you don't need to specify the day...

=YEAR("1/"&A1)

EDITED: To remove stray quote (").
This message was edited by Mark W. on 2002-06-07 08:08
 
Upvote 0
On 2002-06-07 07:59, Mark W. wrote:
Hmmm, =YEAR("01-01-"&A1), doesn't that take you back to where you started? :smile:

BTW, you don't need to specify the day...

=YEAR("1/"&A1")
This message was edited by Mark W. on 2002-06-07 08:01
Click to expand...

Yes, I was kind of trying to make that point with that one. Good point with the day. You may enjoy this one I offered a few days ago, I suggested
=SUBSTITUTE(A1,CHAR(CODE(RIGHT(A1))),"")
in leui of
=SUBSTITUTE(A1,RIGHT(A1),"")
 
Upvote 0
I wanted to keep cell A1 solely for the 4 digit year since that will be the basis for my calculations. Mark W's solution will work best for me.

Thanks for everyone's input.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Current financial year and Pior financial year
Replies
3
Views
107
Sarah7828
S
T
  • Question Question
Help with Calculating Median per Group per Year
Replies
1
Views
80
Kaper
Kaper
D
  • Solved
Need help creating a formula based on VBA function!
Replies
5
Views
372
Trixterz
T
S
  • Question Question
Enter a YYYY in A1, compare to current year in A2
Replies
8
Views
244
Peter_SSs
Peter_SSs
A
  • Solved
Values Mismatch
Replies
3
Views
191
Alex Blakenburg
A

Forum statistics

Threads
1,224,802
Messages
6,181,054
Members
453,014
Latest member
Chris258

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top