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.

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

Then copy,

paste special,

values.MIKE

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

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.

where do I paste the =

DATE(MID(A1,1,4),MID(A1,5,2),MID(A1,7,2))In any convenient

open/unoccupiedcell.Then to get it back into it's original cell location:

Highlight your new date cell and

On the Task Bar

Edit

CopyMove to the original cell location and

Edit

Paste Special

Select Values

Click OKYou 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

Thank you so much Mike - It worked flawlessly! I sincerely appreciate your assistance.

Warmest Regards,

Chris

Ask Your Question

Weekly Poll

Do you think Jony Ive could make a big impact on Airbnb?

Discuss in The Lounge

Poll History