Concatenate numbers from diff columns to create a date?!?

[ahayes3]

I've got 3 columns- the first is a number for the month, the 2nd with the day, the 3rd with a year. I need to Concatenate the 4th column so it reads as a date using the first 3 columns

Example:

mm dd yy DATE
05 30 76 05/30/76

Thanks!!!

ACH in Chicago

 

[jaya]

Try the DATE function. =DATE(year,month,date)

 

[Norie]

If you only want the date as text you could use the following:

=A1 & "/" & B1 & "/" & C1

If you actually want to create a date that Excel will recognise as a date you could use this:

=DATE(C1, A1, B1)

or

=DATEVALUE(A1 & "/" & B1 & "/" & C1)

 

[Scott Huish]

I would go with DATEVALUE, because at least for XL2002, using the DATE function this way with only 1 or 2 digits for the year Excel assumes everything is 1900 something.

Book1
	A	B	C	D	E
1	5	20	7	5/20/1907	5/20/2007
Sheet1

 

[Mark W.]

I would go with DATE because the text string used with DATEVALUE could make the worksheet unportable to another world region. Excel's interpetation of 2-digit years is quite roboust. See the Excel Help topic for "About dates and date systems" and its subtopic, "How Excel interprets two-digit years".

Consider using...

=DATE(IF(C1>29,1900,2000)+C1,A1,B1)

...where C1 contains a year designator that's 2 charactersl or less. This approach gives you full control of the interpetation of 2-digit years (and, as shown conforms to Excel's approach).

 

[Scott Huish]

OK, from the help file:

To ensure that year values are interpreted as you intended, type year values as four digits (2001, rather than 01). By entering four digits for the years, Excel won't interpret the century for you.

As seen in the example I posted, using DATE Excel interpreted 7 as 1907, I tried a lot of combinations and they all resulted in 1900 something, DATEVALUE interpreted the century correctly (the way Excel normally does when directly entering a date).

 

[Mark W.]

HOTPEPPER, rather than making additional postings I just re-edited my posting above a few times (until I was happy with my wording, spelling, etc.)

 

[Scott Huish]

I agree that would work well.