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

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

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
 

Forum statistics

Threads
1,186,163
Messages
5,956,289
Members
438,245
Latest member
bhavdip_mangukiya

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