Date formats?

gg

Well-known Member
Joined
Nov 18, 2003
Messages
560
I am having trouble getting cells to read as dates.

Currently I have in column A

1/10/2004
thru
2/6/2004

I am wanting to sort these asending however, it is only reading the
first letter of the date.

This data was built by and web query that seems to have chaged the format to something else. ( Raw Text, Not date number)

Example:
1/10/2004 as text -->37996 = OK
1/10/2004 as text--> 1/10/2004 = NG

Any ideas how to make these read as a date.
Format cells--> date is already done.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Positive Parrot

New Member
Joined
Jan 16, 2004
Messages
35
gg,

I've had a similar experience and used the VBA DateValue function to get the dates in the right format.

For example if your problem date is in cell A1 try a macro

Code:
Sub ChangeDate
Range("B1").Value = DateValue(Range("A1").Value)
End Sub
and format cell B1 to be a date in whatever format you want.

Regards,

PP
 

gg

Well-known Member
Joined
Nov 18, 2003
Messages
560
That works great..

One more question.
How do I get it to do about 1000 in a row is there an easy way?
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,022
Select all the cells, then use Data | Text to Columns... fixed (though delimited would work too if you do it right) and then select the appropriate Date Option. That'll force it over to dates.
 

Positive Parrot

New Member
Joined
Jan 16, 2004
Messages
35
gg,

Greg's solution is neat but if you want to extend my macro solution from earlier then try something like

Code:
Sub ChangeDates

For i=1 To 1000
Range("B" & i).Value = DateValue(Range("A" & i).Value)
Next i

End Sub

Regards,

PP
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,161
Messages
5,768,539
Members
425,480
Latest member
br400821

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top