Excel Formatting Dates

April 4, 2009 at 15:35:09
Specs: Windows XP, 1.0 Gb
In an Excel spreadsheet that is replicating a printed form, I needed to have the date arranged in three cells:

A1: month i.e., 03
B1: day, i.e., 05
C1: year, i.e., 09

Another section of the same spreadsheet requires the date in 00-00-00 form. How can I combine these three cells to get a date in this format, i.e., 03-05-09?

I tried CONCATENATE but could not get the dashes.

Thank you.


See More: Excel Formatting Dates

Report •


#1
April 4, 2009 at 16:58:17
If it's OK that the result is text, try:

=CONCATENATE(A1,"-",B1,"-",C1)

or

=(A1&"-"&B1&"-"&C1)

If you really want a date, try this:

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

with a Custom format of mm-dd-yy


Report •

#2
April 4, 2009 at 19:35:20
Here's another way you might find interesting:


Cell A1 contains the date your going to break apart:

IE: 04-Apr-09 (The date format does not matter)

First Custom Format Cells B1, B2, B3 with the format 00 (Zero Zero)

Then to break apart the date into it's component parts:

In Cell B1 =MONTH(A1)
In Cell B2 =DAY(A1)
In Cell B3 =TEXT(A1,"yy")

To put all back together in the format MM-DD-YY

In Cell B5 =TEXT(B1&"-"&B2&"-"&B3,"mm-dd-yy")

There is a =YEAR function, but it will return the complete year 2009.


I must say though, I do prefer DerbyDad03's solution for putting the date back together.

MIKE

http://www.skeptic.com/


Report •

#3
April 5, 2009 at 07:34:41
Thank you for your responses.

Report •

Related Solutions


Ask Question