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

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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? :)

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? :)

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

L
  • Solved
Would like to make edits to VBA Calendar Code
Replies
8
Views
416
Legacy 352679
L
J
  • Question
What changed with the Decimal Points
Replies
1
Views
307
6StringJazzer
6StringJazzer
J
  • Solved
DATEDIF formula with TODAY's date
Replies
4
Views
179
Alex Blakenburg
A
D
  • Question
Excel user defined function working on Windows, but does not work on a Mac
Replies
2
Views
116
drWireMORE
D
B
  • Question
Entering only Year as a date entry in cell
Replies
7
Views
821
Joe4
Joe4

Forum statistics

Threads
1,215,148
Messages
6,123,301
Members
449,095
Latest member
Chestertim

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