Excel Date Formatting Question

Microsoft Excel 2003 (full product)
March 12, 2010 at 11:34:15
Specs: Windows XP, 1 gb
I have a quick question for an excel expert.

I have date as 20040314

How do I format it to be 03/14/2004?

Please help.


See More: Excel Date Formatting Question

Report •

#1
March 12, 2010 at 12:23:32
Try this:

= DATE(MID(A1,1,4),MID(A1,5,2),MID(A1,7,2))

Then copy,
paste special,
values.

MIKE

http://www.skeptic.com/


Report •

#2
March 12, 2010 at 12:36:08
Hi,

First you have to convert the text (20040314) into a date that Excel can use.
If 20040314 is in cell A1 put this formula in another cell:

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

Now format that cell with a date format, or a custom format such as "mm/dd/yyyy" and the cell will display 03/14/2004.

If you want to delete the original, then copy the cell with the formula and Paste Special - 'Values and Number Formats', back into either the same cell or the cell that had the original text.

The new date information is now in a form recognized by Excel.

If you did the Paste Special back into cell A1 enter =A1 +1 in another cell which yields 03/15/2004, i.e., 1 day later.

You will be able to add and subtract dates to get the difference in days, and several other date functions, such as WEEKDAY(A1) which returns 1, and formatted as "dddd" it displays Sunday.

Regards



Report •

#3
March 13, 2010 at 22:16:00
You guys are great!!! I hate to be such a newby but Mike ,
where do I paste the =
DATE(MID(A1,1,4),MID(A1,5,2),MID(A1,7,2)) . I sincerely
appreciate your time.

Report •

Related Solutions

#4
March 14, 2010 at 12:09:45
where do I paste the =
DATE(MID(A1,1,4),MID(A1,5,2),MID(A1,7,2))

In any convenient open/unoccupied cell.

Then to get it back into it's original cell location:

Highlight your new date cell and
On the Task Bar
Edit
Copy

Move to the original cell location and
Edit
Paste Special
Select Values
Click OK

You should see the number 38060,
Excel dates are counted from Jan 1 1900,
so the date 3/14/2004 is 38,060 days from Jan 1 1900.

You will need to reformat the cell as a date using the Format, Cell option on the task bar.

MIKE

http://www.skeptic.com/


Report •

#5
March 15, 2010 at 14:26:24
Thank you so much Mike - It worked flawlessly! I sincerely appreciate your assistance.
Warmest Regards,
Chris

Report •

#6
March 15, 2010 at 15:24:28
Glad I could help.

MIKE

http://www.skeptic.com/


Report •

Ask Question