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

ahayes3

New Member
Joined
Mar 1, 2005
Messages
1
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
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,230
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
ABCDE
152075/20/19075/20/2007
Sheet1
 

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
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.)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,271
Messages
5,600,651
Members
414,399
Latest member
Ninjee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top