Dates, Formats, and an IF statement!

[Guest]

Hi,

I have extracted date information from various different databases and wish
to determine which are greater than a certain date. Now, I can write the IF
statement (IF(G2>J1,True,False), but it doesn't seem to be working correctly,
it gives all dates as True, even when they are less than my cutoff date.

I think that there could be a problem with my imported dates, but I cannot
convert them using DATEVALUE - some convert, some give the #VALUE error.
If I convert using =TEXT(G2,"dd/mm/yyyy"), I still can't either work the IF
statement, or use DATEVALUE!

A problem is I'm not sure exactly how the date info is held in the databases,

Help, I think I'm going mad.....

Charlotte

 

[Dave O]

It sounds like you have a mix of formats coming from the external
databases, and you may need to perform different conversions based on
each entries current format.

As a starting point, recall that in Excel, a date is an integer number
and the cell that holds that number is formatted to show a date. For
instance, the integer equivalent of 8/29/2006 is 38958. (Try it: enter
today's date in cell A1, then in C1 enter the formula =A1 and format C1
as a number.)

You can check to see which cells will require conversion with the
formula
=ISNUMBER(A1) which gives a True / False response: the False entries
will need conversion.

You're on the right track with the DATEVALUE conversion. The
=TEXT(G2,"dd/mm/yyyy") conversion yields a text answer, which will not
correctly compare to a numeric date entry. You may want to check those
cells for a leading apostrophe in the cell, or leading or trailing
blank spaces, or other text type entry that would prevent it from
evaluating as a number.

 

[Dave Peterson]

Another way to convert text "dates" to real dates (if the dates are in a single
column).

Select the column
data|text to columns
fixed width
(remove any lines excel guessed)
choose the correct format to match the values in the cell mdy, dmy, ...

Then finish up and format the cells the way you like (dmy maybe??).

Another way if your windows regional date setting matches the same order as the
text dates.
select the range
edit|replace
what: /
with: /
replace all

Excel will see that you're reentering the values and notice that they're dates.

Don't do this if the windows regional date setting isn't the same order as the
text values.

 

[Guest]

Thanks Dave,

ISNUMBER is giving me a FALSE return, so at least I now know that it is not
a number, and I'm not going off my head!

I have tried TRIM and CLEAN, and it still isn't a number. I have performed
a Paste Special - Values, and it's still not a number! I have error tracking
on, looking for a number stored as text, and it's not working - ISNUMBER
still informs me it's not a number, I have tried (obviously) using the Format
Cells option to convert to a date, but that's not helping matters!

There are no apostraphes appearing in the formula bar, so I haven't tried to
remove them...

So the short query is ... how do I get this text to a number now (DATEVALUE
is still working for a small number of dates, aropund 1 in 5)

Thanks again.....

 

[Dave O]

Step 1, next, is to try Dave Peterson's suggestions. Certain cells in
your spreadsheet may be formatted as text, and when numbers are entered
into a text-formatted cell they become text as well, even though they
resemble numbers on screen. This would explain why your paste
special/as values didn't work, if you tried pasting them into their
original locations.

Another possibility is to highlight the dates and run the following
code, which stores the cell's entry to memory, reformats the cell, then
re-populates the cell with the cleaned-up version of the original
entry. CAVEAT: I tested this code and it did the job, but if you try
this you should run it on a back-up copy of your file so you have a
fallback position in case of a catastrophic failure.

Sub Format_Selected_Cells()
Dim rCell As Range
Dim TrueVal As Variant

For Each rCell In Selection.Cells
TrueVal = Trim(rCell.Value)
rCell.ClearContents
rCell.NumberFormat = "dd/mm/yyyy"
rCell.Value = TrueVal
Next rCell


End Sub

 

[Guest]

Thanks to both Dave O & P!

The spreadsheet is now calculating beautifully....after running the Data -
Text to Columns solution. I didn't need to go as far as to rund Dave O's
code - and I agree with the Caveat to create a backup. I always do so when
trying something new & unusual (to me) in Excel. Data is too precious to
muck about with.

of course, the data could always have be extracted again!

Thanks again for all your help

Charlotte