Hi all. Withing a sheet, there are multiple instances of a date being
formatted as text (within a cell, '10/2004). I can use the datevalue
function to convert them. But, they occur in several spots, and their
locations vary from month to month. Is there a way to scan an entire sheet,
find dates formatted as text (ie look for a "/"), and convert them to date
format so my lookup formulas work?

On Mon, 31 Oct 2005 13:53:20 -0500, "Steph" wrote:

Hi all. Withing a sheet, there are multiple instances of a date being
formatted as text (within a cell, '10/2004). I can use the datevalue
function to convert them. But, they occur in several spots, and their
locations vary from month to month. Is there a way to scan an entire sheet,
find dates formatted as text (ie look for a "/"), and convert them to date
format so my lookup formulas work?


You could try this macro:

To enter it, <alt<F11 opens the VB Editor.

Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and paste the code below into the window that opens.

Change AOI to reflect the range you need to check.

<alt<F8 opens the macro dialog box. Select this macro by name, and RUN.

==================================
Option Explicit

Sub ConvDates()
Dim AOI As Range
Dim c As Range

Set AOI = [A1:Z100] 'set this to the maximum range that may include dates

For Each c In AOI
If IsDate(c.Value) Then c.Value = CDate(c.Value)
Next c
End Sub
=============================


--ron

select the offending columns/locations/areas and format as date. (You don't
have to restrict selection to each individual cell, just don't select areas
that would be adversely affected by the formatting to date or the replace
command).

then do Edit=Replace
Replace What: \
Replace With: \
this should cause the date to be reevaluated and stored as a date.

--
Regards,
Tom Ogilvy

"Steph" wrote in message
...
Hi all. Withing a sheet, there are multiple instances of a date being
formatted as text (within a cell, '10/2004). I can use the datevalue
function to convert them. But, they occur in several spots, and their
locations vary from month to month. Is there a way to scan an entire
sheet,
find dates formatted as text (ie look for a "/"), and convert them to date
format so my lookup formulas work?