Solved Convert Date Sequence

July 6, 2011 at 04:41:49
Specs: Windows 7
I have a file which contains dates both in the format 2005/01/01 and 01/01/2005. I need to get the 01/01/2005 cells to reflect the 2005/01/01 format so that the database can easily be sorted by date. Any ideas?

Thank,
Frik


See More: Convert Date Sequence

Report •

✔ Best Answer
July 11, 2011 at 05:04:26
I just tried what I suggested in Excel 2010 and everything worked just as I described, including the Sort dialog box.

Since I was able to convert the text strings in the file you sent me in both 2003 and 2010, there's only 2 other things I can suggest:

1 - Change the date format in your Regional and Language Options control panel to MM/dd/yyyy so it matches mine.

2 - Try my steps on someone else's machine.

If neither of those work, I have no further suggestions. I'm using your spreadsheet and I can convert the text strings to dates and sort them, so I don't know what else to offer.

Good luck!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
July 6, 2011 at 06:15:46
You should first make sure that all your DATES are really dates, not TEXT.

Highlight all of your DATE cells and format them as General.
They should all show as numbers similar to: 40703 which is July 6, 2011.

Excel keeps track of dates by counting the number of days since Jan 1, 1900

This should solve your sorting problem.

Once you know that all your DATE cells are really dates, then do a Custom format of: YYYY/MM/DD

MIKE

http://www.skeptic.com/


Report •

#2
July 6, 2011 at 10:55:50
Dear Mike,

Thanks for the reply.

When formatting as general, the dates which start with yyyy are shown as you indicated above, but the dates ending with the year still shows as dd/mm/yyyy. Any new ideas?

Thanks,
Frik


Report •

#3
July 6, 2011 at 14:00:54
but the dates ending with the year still shows as dd/mm/yyyy

If you format the Date cells as General and it does not convert to a number then it's not a date, it's a TEXT string that looks like a date.

You will need to covert the TEXT string to a real date.

If cell A1 contains the TEXT string: 07/06/2011
in cell B1 enter the formula: =DATEVALUE(A1)

That should covert your TEXT string date into a real DATE.

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
July 7, 2011 at 03:05:51
Thanks again.

I think I'm doing what you're saying, but still no luck.

Thanks,
Frik


Report •

#5
July 7, 2011 at 03:29:53
What happens when you use the DATEVALUE() function?

MIKE

http://www.skeptic.com/


Report •

#6
July 7, 2011 at 03:47:32
Hi Mike,

#VALUE!

Thanks,
Frik


Report •

#7
July 7, 2011 at 03:54:30
A #VALUE error usually occurs when you perform a mathematical operation.

What else is going on that we don't know about?

MIKE

http://www.skeptic.com/


Report •

#8
July 7, 2011 at 04:07:04
Got no idea - have tried all the format cells options (e.g. date, general, text, scientific, etc), but the content looks exactly the same for each option.

Report •

#9
July 7, 2011 at 05:13:15
You may have hidden characters in the cell.

Try using the =CLEAN() function to remove any stray characters.

If your data is in cell A1 then in B1 enter: =CLEAN(A1)
then do a copy / paste special / values
then try the =DATEVALUE() function

MIKE

http://www.skeptic.com/


Report •

#10
July 7, 2011 at 05:23:02
Thanks for all the trouble.

Still gives me #VALUE!


Report •

#11
July 7, 2011 at 07:19:43
OK, lets see if we can break it apart and make a new date.

With your date data in cell A1 in the format of: 01/01/2011
in cell B1 enter the formula: =VALUE(LEFT(A2,2)&"/"&MID(A2,4,2)&"/"&RIGHT(A2,4))
you should get the serial number of the date, IE for 01/01/2011 it would be 39082
Now do a Copy / Paste Special / Values and you should have your data as a real date.
Format the cell as you like.

EDIT ADDED:
Formula was incorrect.
Try this: =VALUE(LEFT(A1,2)&"/"&MID(A1,4,2)&"/"&RIGHT(A1,4))

MIKE

http://www.skeptic.com/


Report •

#12
July 7, 2011 at 10:29:17
Mike:

re: "A #VALUE error usually occurs when you perform a mathematical operation"

The #VALUE error can also occur when the wrong type of argument is used in a function.

Therefore, DATEVALUE will return a #VALUE error if the argument is not in a format that Excel recognizes as a date string.

In fact, if you use DATEVALUE on a cell that contains an actual date, it will return #VALUE since it doesn't recognize the internal value (38354) as a "date string".

Frik:

Try this just as a test:

If your "dates" start in A1, put this in B1 and drag it down:

=ISTEXT(A1)

Wherever you see TRUE means that the reference cell contains text. Do you get any TRUE's?

Also try this:

=DATEVALUE(MONTH(A1)&"/"&DAY(A1)&"/"&YEAR(A1))

This should return numbers like Mike mentioned (38353) assuming that there are no extraneous characters as Mike also mentioned.

Then do a Copy...PasteSpecial...Values and format them as dates in whatever format you choose.

That seems to work whether my strings were formatted as Dates or as Text.

If there are hidden characters or leading spaces, one of these might help...they did for me:

=DATEVALUE(MONTH(TRIM(A1))&"/"&DAY(TRIM(A1))&"/"&YEAR(TRIM(A1)))

=DATEVALUE(MONTH(CLEAN(TRIM(A1)))&"/"&DAY(CLEAN(TRIM(A1)))&"/"&YEAR(CLEAN(TRIM(A1))))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#13
July 7, 2011 at 12:08:17
DerbyDad03:

if you use DATEVALUE on a cell that contains an actual date, it will return #VALUE since it doesn't recognize the internal value (38354) as a "date string"

I understand that, but the OP stated:

the dates ending with the year still shows as dd/mm/yyyy

A TEXT string formatted as above should be recognized by DATEVALUE,
since it was not, I thought there may be some extraneous non printing characters in the cell, so suggested =TRIM().

MIKE

http://www.skeptic.com/


Report •

#14
July 7, 2011 at 13:13:43
I agree...wasn't arguing at all. Thus my CLEAN(TRIM...)) suggestion which should get rid of any and all extra stuff - which I gave you credit for bringing up. ;-)

My other point was simply that #VALUE can occur even when there is no "mathematical operation" involved.

That's why I suggested that forcing both actual dates and "text dates" to be text dates via MONTH(A1) & "/" etc. should (and I emphasize should) allow DATEVALUE to work on any kind of entry.

Since DATEVALUE will return #VALUE when it encounters a real date, I was hoping that my suggestion would handle both cases.

P.S. I hate working with dates in Excel for this very reason.

Hint, in case the OP is listening: Maybe if the spreadsheet was posted someplace we could download it and stop guessing at what might be going on.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#15
July 8, 2011 at 01:33:07
2001/10/08
2003/01/13
2003/06/09
2003/10/01
2004/01/05
01/01/2005
01/01/2007
01/01/2007
01/01/2007
01/01/2007
01/01/2007
01/01/2007
01/01/2007
01/01/2007


Report •

#16
July 8, 2011 at 01:36:25
Thanks guys.

I have entered a sample of my data above. The first couple are the ones which sort easily and how I'd like it to look, whereas the last couple are the ones I'd like to change.

Thanks,
Frik


Report •

#17
July 8, 2011 at 04:15:55
Frik,

It's not about what the dates looks like in this forum, it's about how Excel is treating the "dates" in the cells.

If none of the suggestions from either Mike or I have worked, then there is something about the data or formatting that is causing the issue.

Did you try everything has been suggested by us?

If I sent you an email address via private message, could you send me the actual spreadsheet - with any confidential data deleted of course?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#18
July 8, 2011 at 04:20:32
Please do send me your e-mail address.

Thanks,
Frik


Report •

#19
Report •

#20
July 8, 2011 at 05:52:51
I had a chance to look at your speadsheet.

When I opened it in Excel 2003 (the only version I have access to at this time) Column B looked like this:

10/08/2001
01/13/2003
06/09/2003
10/01/2003
01/05/2004
01/01/2005
01/01/2007
01/01/2007
01/01/2007
01/01/2007
01/01/2007
01/01/2007
01/01/2007
01/01/2007

I'm assuming that I do not get any entries that start with YYYY because the date setting in my Regional and Language Options control panel is set to MM/dd/yyyy.

Using the ISTEXT function, I found that everything from 1/1/2005 (B9) and below are text values, not dates. Row 9 is also the row where your other data goes from all upper case to mixed case, so I assume that this data was pasted from some other source.

When I tried to sort the data on Column B (Doe) Excel popped up a message that said:

The following sort key may not sort as expected because
it contains some numbers formatted as text:
Doe
What would you like to do?
O Sort anything that looks like a number as a number
O Sort numbers and numbers stored as text separately

When I choose the first option (Sort anything...) it sorted them fine.

So, as Mike suggested a while back, some of your values are dates and some of them are text strings.

To convert the text strings to dates, this worked for me:

1 - Select an empty cell
2 - Ctrl-c to copy
3 - Select the values in the Doe column (Both dates and the text strings)
4 - Right Click...Paste Special...Add (All values should change to a 5 digit number)
5 - Format the values in any date format you'd like
6 - Sort

Since Excel would "prefer" to work with numbers instead of text, it will attempt to add zero to the "numbers stored as text" and convert them to numbers. Once they have been converted, they can be formatted as dates.


You should also be able to convert the text values to dates by doing this:

1 - Enter this formula in Row 4 of an empty column and drag it down:

=DATEVALUE(MONTH(B4)&"/"&DAY(B4)&"/"&YEAR(B4))

2 - Right Click...Copy...Right Click...Paste Special...Values
3 - Format the 5 digit numbers in any date format you'd like

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#21
July 8, 2011 at 06:01:29
Thanks for ALL the trouble - running out of the office now, but will try over the weekend.

Best regards,
Frik


Report •

#22
July 11, 2011 at 00:42:12
Tried everything you suggested, thanks.

When sorting, I don't get the "sort key may not sort as expected" message with the options.

When converting with Paste Special, the "problem cells" return empty cells - not even an error message.

When trying to convert the text values with your formula, still returns #VALUE!

I also saved the file in 2003 and tried all the above, with the same results.


Report •

#23
July 11, 2011 at 05:04:26
✔ Best Answer
I just tried what I suggested in Excel 2010 and everything worked just as I described, including the Sort dialog box.

Since I was able to convert the text strings in the file you sent me in both 2003 and 2010, there's only 2 other things I can suggest:

1 - Change the date format in your Regional and Language Options control panel to MM/dd/yyyy so it matches mine.

2 - Try my steps on someone else's machine.

If neither of those work, I have no further suggestions. I'm using your spreadsheet and I can convert the text strings to dates and sort them, so I don't know what else to offer.

Good luck!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#24
July 11, 2011 at 07:09:43
Thanks, the regional settings is what did it.

Report •

#25
July 11, 2011 at 08:40:38
Well, I did mention that in my earlier response...

Anyway, I'm glad the issue is resolved.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#26
July 11, 2011 at 11:51:02
Meant the regional settings, along with the other input (especially in response 20 above), is what did it - the regional settings was the final straw ...

Report •

Ask Question