Find a Date when "d" formated

[Aknak]

Hi,

I have one excel sheet with the date 30/01/2021 custom formated with "d" to show me only the day (30).

When I try to find the same date, in the format 30/01/2021, with "Find and Select" I get the message:

"We couldn't find what you were looking for. Click Options for more ways to search."

What I need to do to find it?

And, how I can find the same with VBA code "selection.find" or "cells.find" ?

Kindly, someone can help me?

Thanks in advance.

Luis

 

[Aknak]

Find searches for what appears visually and so a date with the column width not wide enough will not be picked up as it displays multiple ##.
On the same principal with your original data try putting 30 in the find what box, also @Alex Blakenburg suggested works for me.

Hi Mark,

If I try to find 3, 30 or another day also works for me, but I won't find the full date in format dd/mm/yyyy) when the cell shows only the number of the day.

Because if you select the range and search you got 2 results if you find by "30".

To understand better, the white cells in the range have one formula and a conditional formating to fill white if the date is not a day of the month, in this case, January .

More interesting If I use VBA,

I got the correct value, find the short date and return the value. Why in excel I can't?

Thanks for your atention.

Ak
Lisbon / Portugal

 

[MARK858]

Try setting your options to formulas rather than Values and are your Regional settings in Windows for short date set as dd/mm/yyyy ?

 

[Aknak]

Try setting your options to formulas rather than Values and are your Regional settings in Windows for short date set as dd/mm/yyyy ?

Hi,

Yes. please check #9, in this post.

Ak

 

[MARK858]

Doesn't answer whether you have the options set to formulas, in post 11 they are set as Values

 

[Aknak]

Hi,
Now with formulas in the options.

Ak,

 

[Alex Blakenburg]

I think we are going to have to just agree its quite quirky.

Find Lookin Formulas - Constants
If you use the Short Date per your Region Setting (the year has to be 4 digits) in the Find what box
This will find all dates that are not based on formulas regardless of how the date is formatted.
It will not find dates that are the result of formulas

Find Lookin Values
Unlike the above, this will only find dates formatted the way you type it into the Find what box.
If you have dates that are the result of a formula you have no choice but to use this. It will find any dates formatted the same you have represented it in the Find what box regardless of whether they are a constant or a result of a formula.

VBA is consistent with the above

Find on Android
The find here automatically switches between both the above.
If in the find box you type in the Region formatted date, it will find all the constants regardless of the formatting but only the results of formulas that match what you typed into the find box. There is no manual option to select formulas or values