Excel formula question - Convert to specific date format?

Question

I have data in the format YYYYMMDD which I need converted to MM-DD-YYYY. I need a formula solution in Excel.

For example,
20070214 -----> 02-14-2007

Assume data is in Column A and the new data will appear in Column B.

First person with a WORKING formula get 10 points.

Answer 1

Assuming your first value is in cell A1, then the formula to convert it to just the format you want is:

=VALUE(MID(A1,5,2)&"-"&RIGHT(A1,2)&"-"&LEFT(A1,2))

There are both more complicated formulas that will allow you to specify a formula in a separate column & then combine the formula & text to get the correct output and ways to do this by playing with the date format (Western US, European, etc.), but this is the simplest way to convert between the two formats you list.

EDIT: Yahoo keeps cropping my formula. Paste the following lines together with no extra spaces:

=VALUE(MID(A1,5,2)
&"-"&RIGHT(A1,2)
&"-"&LEFT(A1,2))

Answer 2

Excel Formula Date Format

Answer 3

Excel Date Format Formula

Answer 4

firstly there is no need to copy with format painter, an easier option is to highlight the cells/rows/collumns you wish to format , then go format>conditional formatting etc.( for a bunch of randomly located cells hit and hold "ctrl" as you select the cells) 2) You say the cells all turn red, reason behind this maybe be a conflict in your formulas. red, yellow and green are triggered by certain values, but what you have to remember is that red and yellow values fall under green values, and red values fall under yellow therefore excel might be getting confused on what color to fill the cell. e.g. if cell t53 < 5 that satisfies all three conditions therefore which rule should excel use ? I would suggest you use if "x" is a value between (-100 and 5) , (5-10) & 10-15 respectively) then condition is ...(red, green yellow etc).... Hope that helps oi , jmorge stop poaching my answer lol

Answer 5

Just set column B equal to column A, then format column b differently. Highlight it, right click-->format cells. On the number tab, select from the preset date types, or go to "custom" and enter mm-dd-yyyy

Answer 6

You don't need a formula..

Type in the cell:

=NOW()

Then right hand click and select format cells to pick the style you want

If the words NAME or ###### appear make the collum much wider, it does work

Answer 7

Assume 20070214 in Cell A1

Two ways to do this
1.) convert Text to Text in a new format using Place following in Cell B1
=MID(A1,5,2)&"/"& RIGHT(A1,2) & "/" & LEFT(A1,4)

2,) Convert text to Excel DATE then Format raw DATE nnumber into ANY valid DATE format
using
=DATE((LEFT(A1,4)),(MID(A1,5,2)),(RIGHT(A1,2)))

edit:

=MID(A1,5,2)
&"/"
& RIGHT(A1,2)
& "/"
& LEFT(A1,4)

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

Place the above formulas all on one line,Stupid Yahoo Truncates code

Answer 8

Go to format cells then click on date.

Answer 9

FORMAT your CELL by going to the FORMAT menu, selecting the "CELLS" option, Selecting the "DATE" option from the left listbox, and selecting your preferred format from the listbox on the right.

Answer 10

Here's your formula, assuming the 20070214 is in cell A1

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

Dont forget to format your cell properly