Finding dates without attention to data formats

[Walter Briscoe]

I am using Microsoft Office Excel 2003 (11.8346.8341) SP3.
I have a sheet which represents a calendar.

The following should help give a picture of that calendar:

B ... DK DL
1 12
2 May
....
16 Tue 15 Data for 15/05/12
17 Wed 16 Data for 16/05/12
18 Thu 17 Data for 17/05/12

There are formulas (sic - I prefer formulae) and data formats involved
DK17 is =DK16+1 and has a format of dd and so appears as 16, but
actually represents 16/05/2012 (or 05/16/2012 in American).

I want to search for a date with the "Find and Replace" dialog.
e.g. I want to find that 16, by searching for 16/05/2012 or 05/16/2012.
I can do so, if I change the format to dd/mm/yyyy.
Searching Values seems to search for the rendering of the cell.
Searching formulas seems to search formulas. e.g. I can search for DK16.

I suspect I can't search for either 16/05/2012 or 05/16/2012 and match
that 16 which displays on the screen.

However, it seems worth asking the question.
I had been looking for the meaning of Formulas in the "Find and Replace"
dialog, without success. I now understand it.

So, "Look in" has logic
Formulas Search Formulas
Values Search Values as displayed with data formats.
Comments I assume this searches Comments attached to cells.

I want to be able to search for
TrueValues Search Values without attention to data formats.

I hope I have explained my need and would appreciate light on it.

 

[Walter Briscoe]

In message <[email protected]> of Wed, 16 May
2012 11:04:09 in microsoft.public.excel.worksheet.functions, Ron

The following applies to Excel 2007, but I don't believe there have
been changes in this feature since 2003.

Why do you need to search by formulas? Why not just search by Values,
if what you are looking for is a "16" being displayed in the cell. If
I search by values, and have a "16" visible in the cell, Excel will
FIND it no matter if that 16 is the result of a formula, or just a
date.

I have data elsewhere, which is tagged with a date.
I want to be able to transcribe that data automatically.

Currently, if I want to write "foo" next to that 16 in May in 2012, I
might do
Rows(1). _
Find(What:="12", LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range(ActiveCell(2, 1), ActiveCell(2, 24)). _
Find(What:="May", LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Columns(ActiveCell.Column). _
Find(What:="16", LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
acivecell(1, 2) = "foo"

That seems excessive, given that "16" actually holds a date which has
the format "dd". If it had the format "yyyy/mmm/dd", it would be
displayed as "2012/May/16" and a single find() call would work.

The Format function applied to cells might do the job, but format()
returns a string where returning a range would be needed.
e.g. with different semantics, I might do
format(cells, "yyyy/mmm/dd").find(What:="2012/May/16", ...

I suspect that there is no easy way to do what I want, but I ask to
confirm that.

I have wandered into microsoft.public.excel.programming territory.

In your examples above, where the format is dd, again, Excel, with a
search by Values, will Find the 16. No need to change the format.
Of course, if you format the cells as General, then the display might
be 41045 and searching for 16 will not return anything.

If the above suggestions do not help, then, backing up: why do you
want to do the search? Perhaps there is another way of solving your
real problem.

I hope my expansion presents my real problem.