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.

 

[Ron Rosenfeld]

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.

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.

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.

 

[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.

 

[Ron Rosenfeld]

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


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.


I hope my expansion presents my real problem.

OK, there are several problems having to do with how VBA looks at dates and also using a data as a parameter with the .Find method; along with the US-centricity of the VBA date functions in general.
I don't have time to sort it all out this morning, but an alternative, depending on the amount of data, might be to use the Match worksheet function. Something like:

=============================
Option Explicit
Sub WriteFoo()
Const StringToWrite As String = "foo"
Dim FindString As Date
Dim rRangeToSearch As Range
Dim vRangeToSearch As Variant
Dim rFoundCell As Range
FindString = Date - 1
Set rRangeToSearch = Range("A1", Cells(Rows.Count, "A").End(xlUp))
rRangeToSearch.Offset(columnoffset:=1).ClearContents

vRangeToSearch = rRangeToSearch

rRangeToSearch.Cells(WorksheetFunction.Match(FindString, vRangeToSearch, 0), 2).Value = _
StringToWrite

End Sub
==============================

should do what I think you are describing. But I have not checked it with different regional date formats. I'll try to look into this more when I have more time.