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

 

[Alex Blakenburg]

How did you format it as "d" ?
If you used a custom format of d then Find with Look in: Formulas, should find it.
Look in: Values does "not" seem to be able to find it.
If you use =Text(cell_ref,"d") then it is no longer a date and find won't find it.

 

[Aknak]

Hi,

Thanks for your atention.

"d" format is like this:

Before to do this post I already try what you said to find but I get the message that I said in the previous post.

Thanks.

 

[Alex Blakenburg]

Can you check you have formula selected AND everything under options turned off.

Then show me a screenshot of your 30/01/2021 formatted firstly as the date option with the * in front of it (probably the first date option ). Then formatted as general so I can see the number it shows.

 

[Alex Blakenburg]

I have done some more testing and its a bit of nasty piece of work.
"Find" will work but you need to know your exact regional setting of the short date format.

The Excel version of the Regional format (the short date with the asterisk * prefix) always shows the date of 14 Mar 2017 as the example date and as such may lead you to believe that the day is represented by 2 characters.
Either

• type a date with a day < 10 into Excel and see whether you get for example 5 or 05,
  or
• go to Windows Settings > Region Settings > Change data format.

In the Excel FIND BOX use the regional setting for the day and the month ie with or without leading zeros per your format. The EXCEPTION is always use a 4 digit year.
(it appears to me that both Look in: Values or Formulas should work if you do this)

PS: I have tested with my Regional settings set to Australia in the below, but have also tested with it set to US.

 

[Alex Blakenburg]

Just to clarify, although what is in the Find box needs to be in the Region Settings Short Date format, it will find any date no matter how it is formatted in Excel.

 

[Aknak]

Hi,
Thanks for your attention.
In the next images, I will try to explain better the question.

I have one date in the format dd/mm/yyyy and if i try to find it by 03/01/2021 the find works.

Then I will format in "d" format to show me only the number of the day.

Then I trying to find the date in the format dd/mm/yyy, "look in - Values" and didnt find nothing.

Then more funny, if I reformat again to format dd/mm/yyyy but I fit the column to the cell shows "##" and if I try to find by dd/mm/yyyy again nothing is found.

Then, I think that my region format is ok. I try to find in the correct format but if you have one date the format dd/mm/yyyy
and you format if to "d", if you try to find this date by dd/mm/yyyy nothing is found.
Is this what I try to understand. Why?

Thanks for you patience, but don't spend a lot to time with this. Only to know.

Ak,

 

[Alex Blakenburg]

Thank you Ak for sharing what you are experiencing. Its really helpful.
From a trouble shooting perspective I would love to see a screenshot of the result if you:-
created a new sheet in the workbook and without applying any formatting type in 03/01/2021.

My expectation is that a Default Short Date without the leading zero is more likely and that you will get 3/01/2021.
If it does default to 3/01/2021 can you try that in your find box.
(If it shows something different try whatever it comes back with except use a 4 digit year, so if it display 3/1/21 try 3/1/2021 in the find box)

Thanks
Alex
Sydney Australia

 

[Aknak]

Hello Alex,

As per your desire... No, if I write 03/01/2021 the value in the excel is the same. 03/01/2021.

Please see the image.

Thanks

Luis
Lisbon Portugal

 

[MARK858]

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.