Entering only Year as a date entry in cell

[briggss1]

Want to enter dates as a year only. Then I will set a formula to monitor expirations in that year. Example, in Cell A1, I simply want to enter a purchase date of 2019 (not 1/1/2019, etc.). Just 2019. I have the cell format set to YYYY, but still it requires a full day, month, and year to display only the date. All I want to enter is the date "2019" so that cell B1 will flag it when it goes past a set expiration date of lets say like 5 years. I have B1 working fine, but A1 I cant seem to get dialed in.

If I only enter the year 2019 it returns the 1905 date........

 

[RoryA]

Just format the cell as general. 2019 is not a date, it's just a number that happens to be a year, so it doesn't require date formatting.

 

[briggss1]

I started there, but I need the formulas to add dates to determine the expiration. Using the Now() function doesn't work with General (I don't think)

 

[RoryA]

That doesn't mean anything to me out of context. What formulas?

I can only address the question you ask. If you want to just enter 2019 in a cell, do not format it as any sort of date.

 

[Peter_SSs]

Could it be something like this that you want?

23 05 03.xlsm
	A	B	C
1	Purchase	Expires in ? Years	Status
2	2019	5	Not expired
3	2018	3	Expired
briggss1

Cell Formulas
Range	Formula
C2:C3	C2	=IF(YEAR(TODAY())-A2>=B2, "Expired", "Not expired")

If not, then what about some small sample dummy data and the expected results with XL2BB so that we can see what you are actually dealing with?

 

[briggss1]

Could it be something like this that you want?

23 05 03.xlsm
	A	B	C
1	Purchase	Expires in ? Years	Status
2	2019	5	Not expired
3	2018	3	Expired
briggss1

Cell Formulas
Range	Formula
C2:C3	C2	=IF(YEAR(TODAY())-A2>=B2, "Expired", "Not expired")

If not, then what about some small sample dummy data and the expected results with XL2BB so that we can see what you are actually dealing with?

yes, exactly. if I enter 2019, it gives the ol' 1905

 

[RoryA]

Because you formatted it as YYYY. Don't do that!

 

[Joe4]

It is important to understand how Excel stores dates. Dates in Excel really are just numbers with special date formats. Dates are stored as the number of days since 1/0/1900.
So if you enter 2019 and format it as a date, it will return the date 2019 days from 1/0/1900, which is: July 11, 1905

So if you just want to enter 2019 for the year, then you do NOT want to apply any sort of date format to it, as Rory instructed. You want to leave it as a number.
If you need that entry to be a date entry for the year 2019, you will HAVE to enter a month/day portion (as you cannot have a date wihtout one!)
Note that you can use VBA to automatically convert your entries to a valid date (i.e. you could enter 2019 and have VBA convert it to 1/1/2019).