Making a date out of three columns that are not necessarily filled.

[mawilson12]

I have a spreadsheet of Civil War veterans which I am trying to get into genealogy software. Converting into real dates is the first task. If I have an Excel spreadsheet that has Column D2 as a day of the month, E2 as the number month of a year and F2 is a year, what formula do I use to have a cell with the date expressed as (d)d/MMM /YYYY compensating for cells that may not have a day or month? Ideally if the day or month is missing, just do month/year or year accordingly. Otherwise, as close as I can get will be fine, even if the date is 1/1/Year.

 

[JoeMo]

I don't have a solution for you, but be aware you are dealing with dates prior to the start of Excel's calendar (1/1/1900 is day1).
This link may be helpful:
Deal With Dates Before 1900

 

[johnnyL]

In addition to what @JoeMo has provided, Due to the missing data for certain columns, I can only see a vba code approach to your solution, but I am not that good with formulas to begin with.

 

[johnnyL]

BTW, believe it or not, I decided to try a formula approach, for now, just to help me learn a bit. The following is what I have come up with thus far for your data:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Fname	Mname	Lname	BirthDay	BirthMonth	BirthYear	DeathDay	DeathMonth	DeathYear	Age	Best Birth Date
2	William		Abbie			1843	3	1	1863		1/1/1843
3	James	B.	Acton			1841	10	8	1896		1/1/1841
4	Samuel		Adams			1844	7	3	1867		1/1/1844
5	Thomas		Adams			1839	21	11	1862		1/1/1839
6	William		Adams				21	1	1865		No Birth Date Available
7	Paul		Albert	5	8	1845	25	1	1865		5/8/1845
8	William	D.	Alden			1829	1	6	1865		1/1/1829
9	James	Percival	Allen	22	1	1842	21	5	1916		22/1/1842
10	William		Allen			1844	7	5	1866		1/1/1844
11	Caleb	L.	Anderson			1824	24	8	1863		1/1/1824
12	Bartlett		Armbruster	23	8	1843	26	3	1923		23/8/1843
13	Chauncey		Atwell	10	11	1825	13	7	1864		10/11/1825
14	Francis		Bache								No Birth Date Available
15	John		Booth	2	4	1905	9	6	1997		2/4/1905
16
Sheet1

Cell Formulas
Range	Formula
K2:K15	K2	=IF(AND(D2=0,E2=0,F2<>0),"1/1/"&F2,IF(AND(D2=0,E2=0,F2=0),"No Birth Date Available",IF(AND(D2<>0,E2<>0,F2<>0),D2 & "/" & E2 & "/" & F2,"")))

 

[Peter_SSs]

@mawilson12
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also a good idea to provide sample data in a form that helpers can actually use to test solutions.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[mawilson12]

@mawilson12
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also a good idea to provide sample data in a form that helpers can actually use to test solutions.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Thanks. Unfortunately, my Excel baulked at at the XLBB Add-in originally, indicating it didn't come from a signed source. I will update my account, however.

 

[johnnyL]

@mawilston12 did the formula I provided not get you some dates to use as the birth date?

 

[Peter_SSs]

my Excel baulked at at the XLBB Add-in originally, indicating it didn't come from a signed source.

So did you try to install it anyway given that we have hundreds if not thousands of users here using it without any security issues being apparent?

I will update my account, however.

Thank you for doing that.

@mawilston12 did the formula I provided not get you some dates to use as the birth date?

If text dates are acceptable, then an alternative might be as follows.
If date order of m/d/y is required then swap the order of the two MAX functions in the formula.

23 05 14.xlsm
	D	E	F				J	K
1	BirthDay	BirthMonth	BirthYear				Age	Best Birth Date
2			1843					1/1/1843
3			1841					1/1/1841
4			1844					1/1/1844
5			1839					1/1/1839
6								No Birth Date Available
7	5	8	1845					5/8/1845
8			1829					1/1/1829
9	22	1	1842					22/1/1842
10			1844					1/1/1844
11			1824					1/1/1824
12	23	8	1843					23/8/1843
13	10	11	1825					10/11/1825
14								No Birth Date Available
15	2	4	1905					2/4/1905
Dates

Cell Formulas
Range	Formula
K2:K15	K2	=IF(F2="","No Birth Date Available",MAX(D2,1)&"/"&MAX(E2,1)&"/"&F2)

 

[mawilson12]

So did you try to install it anyway given that we have hundreds if not thousands of users here using it without any security issues being apparent?


Thank you for doing that.

No, as at the time I was on a work computer, if you must know. No need for you to be a smart-aleck about it.

I appreciate you pointing out the additional information options, I hadn't noticed it as I was a user prior to the breach and I didn't recall that being there. Thanks for pointing out another option.

 

[johnnyL]

@mawilson12 I think you combined two responses into one that doesn't reflect what @Peter_SSs said.

The 'Thank you for doing that" response was directed at you updating your account.