help with this formula

June 21, 2012 at 12:08:02
Specs: Windows Vista
I've been trying to covert multiple, different dates in this format 20060101 to 01/01/06 or 1/1/06 and found this thread on this forum, but I can't get it to work. I am not that good with manipulating Excel. My dates start at D3 and run for thousands of rows. They are all different dates that need to be converted, too. Will this be able to convert them all in one shot or would I have to do individually (if I can get the formula to work)? Thanks!

See More: help with this formula

Report •

June 21, 2012 at 12:21:18
First make sure ALL your DATES are really Dates,
not a TEXT string that looks like a date.

To check,
Highlight all your Date cells in column D
Then on the Ribbon,
Select Home
Select Format
Select Format Cells (bottom of popup)
Select the Number Tab
Select Number format from the list
Select OK

If you have a REAL DATE, then you should see a number similar to:


If the cell does not change into a number, it is NOT a date.


Report •

June 21, 2012 at 12:41:03
Thank for that help Mike. Only a few of the "dates" came out with the decimal format. The rest just stayed the same, which apparently means they are not numbers:( Does this mean I am out of luck, then?

Report •

June 21, 2012 at 14:53:56
Does this mean I am out of luck, then?

No, it just means life got a bit more complicated.

First your going to have to figure out what Date Format was used.

If your in the USA, then the default format is MM/DD/YYYY or 06/21/2012
but if your across the pond,
then the European format is 21/06/2012 or DD/MM/YYYY

Hopefully, you don't have a mix of dates.

How are the majority of the dates formatted?

We'll do the most, first.


Report •

Related Solutions

June 21, 2012 at 15:02:44
One quick thing to try:

Use the Text To Column Function and see if that will convert the dates.

Select / Highlight your Date cells
On the Ribbon
Select Data tab
Select Text to Columns
Select Delimited button, click Next
Leave the next window untouched, click Next
In this window select the Date button
and choose your format from the drop box next to it.
Click Finish

See if that works.


Report •

June 22, 2012 at 03:48:26
I am in the US- Eastern. Literally, only 5 of about 10,000 lines changed to a decimal when I did the first test to see if I even had numbers. It looks like they are text and written in each line as 20051231. I did the text to columns, but it didn't give me anything. I'm not quite sure how to figure out what date format was used. Is this right - I went to format cells; number, date is selected in the left hand box, in the right box *3/14/2001. The asterisk means applied formats do not switch date orders with the OS. Ugh! Thanks for helping!

Report •

June 22, 2012 at 05:21:09
Ok, if your date is in this format: 20051231 which is Year Month Day
then try this formula in a blank column:


Drag down as many rows as necessary and see what you get.


You might have problems if the month is abbreviated with only 1 digit, like 2005231
or if the day is abbreviated with one digit,
like 2005122.


Report •

June 22, 2012 at 08:44:54
Nope, it just put the formula in the box itself. I have the equal sign, I'm typing it in the function box. This is Excel 2003 (sorry should have mentioned it before). It doesn't change anything:(

I'll be glad to send it to you to see if you can get it!!

Report •

June 22, 2012 at 08:57:18
Find an open cell some where on your sheet and just Copy/Paste from here,
it's easier and your sure to get the whole formula.

Being on 2003 should not matter, BUT,
just to be sure, make sure you have the Analysis Toolpak loaded.
Not sure if you need it to use the =DATEVALUE() function.

On the Tools menu,
click Add-Ins.

In the Add-Ins available box,
Select the check box next to Analysis Toolpak,
and then click OK.


Report •

Ask Question