CONVERT YEAR to a DATE

[frequency_77]

Hi all,
I thought this would be a straightforward exercise. I have a column that is a YEAR, but it's a GENERAL data type. I need to convert that to a date, basically the last date of the year, 12/31/2018.

example; 2018 --> 12/31/2018

I have a column that has years beginning from 2018 going to 2000. I need to make this a DATA field, with the last day of the year, FOR ALL THE YEARS.

I tried to use CONCANTENATE, but when I use the formula in the first cell, I grip it and copy all the way down the column it just copy's the same value, 12/31/2018.

Thanks for any help,

 

[Dave Patton]

T202010a.xlsm
	A	B
2	2018	31-Dec-18
3	2019	31-Dec-19
4c

Cell Formulas
Range	Formula
B2:B3	B2	=DATE(A2,12,31)

 

[frequency_77]

Thanks Dave.
I tried that. It's basically copying "2018" as the year - for all the cells.

 

[Fluff]

Cross posted convert text YEAR to a date

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered elsewhere.

 

[Norie]

With the year in A1 you could try a formula like this.

=DATE(A1+1, 1,0)

 

[frequency_77]

I think it has something to do with the DATA TYPE, but I could be wrong.

 

[Norie]

Data type shouldn't matter, if it did then it's more likely you would get an error rather than the same value repeated.

Also, in the formula I posted by using +1 the year value should be converted to a numeric value.

 

[Dave Patton]

try the suggested formulas and ensure you format the result to your preference.

Ensure your information such as 2019 does not contain extra characters.

T202010a.xlsm
	A	B	C	D
1	2018	31-Dec-18	31-Dec-17	31-Dec-18
2	2019	31-Dec-19	31-Dec-18
4c

Cell Formulas
Range	Formula
B1:B2	B1	=DATE(A1,12,31)
C1:C2	C1	=DATE(A1,1,0)
D1	D1	=IF(LEN(A1)=4,DATE(A1,12,31),"")