Merging two Date Formats

[Sarah Dwyer]

Hello,

I need to create a column with just a year of birth like '1990' from two columns that currently have a mix of '12/05/2018' and '2018'

for example either column may have 12/0/2018 format another column may have '2018' format I want to keep the '2018' format only in a new column "F' with just '2018'

 

[Peter_SSs]

Welcome to the MrExcel board!

What do you want to do with the row that says 1/05/1986 and 1990 in the other column? Similar for the row where your cursor is shown.

BTW, you will get much better response if you show data that can be copied, rather than just an image from which helpers would have to manually type the data to test.

 

[Sarah Dwyer]

Hello Peter,

Thanks for replying. I want a new column with just the year of birth I've named this Year only below. So if Column A Date of Birth has 01/05/1986 and Column B has 1990 or vice versa, I want column C to return 1990. We only need year of births to be collected not full date of births. This is a database that has been collecting both and we want to re-import the data with just the year of birth. See spreadsheet below. In the column that says Year Only I have typed in what I want the formula to return. I just need the formula. I hope this helps. Really appreciate your expertise.

Date of birth	Year of birth	Year only
1966​		1966​
	1965​	1965​
1/05/1986​	1990​	1990​
1/01/1990​	1990​
	1/01/1984​	1984​
1/05/1971​		1971​
1959​		1959​
19/04/1941​		1941​
	1963​	1963​
12/05/2018​	1/01/1980​	1980​
1993​		1993​
	1979​	1979​
11/02/1935​		1935​
1981​		1981​
1/01/1965​	1/01/1965​	1965​
21/01/1964​	1964​	1964​
	1963​	1963​

 

[Peter_SSs]

We only need year of births to be collected not full date of births.

OK, understand that but how you can have a date of birth of 1/05/1986 yet a year of birth of 1990 is beyond me.

So, see if this is what you want.

Also suggest that you have a look in my signature block below for help with how to post screen shots/data like this.

Book1
	C	D	E	F
1	Date of birth		Year of birth	Year only
2	1966			1966
3
4			1965	1965
5
6	1/05/1986		1990	1990
7	1/01/1990		1990	1990
8
9			1/01/1984	1984
10
11
12	1/05/1971			1971
13
14
15	1959			1959
16	19/04/1941			1941
17
18			1963	1963
19
20
21
22
23	12/05/2018		1/01/1980	1980
24	1993			1993
25			1979	1979
26
27	11/02/1935			1935
28
29
30	1981			1981
31
32
33	1/01/1965		1/01/1965	1965
34	21/01/1964		1964	1964
35			1963	1963
Year only

Cell Formulas
Range	Formula
F2:F35	F2	=IF(C2&E2="","",RIGHT(IF(C2="","",IF(C2<3000,C2,YEAR(C2)))&IF(E2="","",IF(E2<3000,E2,YEAR(E2))),4)+0)

 

[Sarah Dwyer]

Thank you very much.

I tried to download the Add-on but when I got to Browse I did not know what to do next?

 

[Peter_SSs]

when I got to Browse I did not know what to do next?

You need to use that browse window to browse to wherever you saved the Add-In file at the very first Download step and click on it

 

[Sarah Dwyer]

Ok thanks Peter for all your help, much appreciated.

 

[Peter_SSs]

Ok thanks Peter for all your help, much appreciated.

You're welcome. Thanks for the follow-up (on both counts).