Date format changing and preventing a match, when "Reformatting" code is ran

[RockandGrohl]

I have a spreadsheet that a group of people in the business use, they're not very computer savvy or... dare I say... common-sense savvy either so I have to do a fair amount of literal foolproofing.

On this sheet they have times, dates, flight codes etc per customer per line. I have a macro that runs when the spreadsheet opens that reformats things to the "proper" sense of the word, like capitalising some columns, aligning others, etc.


As part of my code is the following:

' DATES

Range("F:G").NumberFormat = "dd/mm/yyyy"
Range("M:M").NumberFormat = "dd/mm/yyyy"
Range("R:R").NumberFormat = "dd/mm/yyyy"

This is ensuring that all dates are present as "11/02/2019"

There is also formatting code that trims spaces, removes spaces, etc.


The way the sheet works is the staff select a product and a date, which are in a user form. The data is generated by going through the sheet and assigning each product + date together.

When the staff select product A, they then select a date and press a button and it displays the information on a new tab.


However, when the staff select product B and select a date, nothing comes up.


BUT.

If I go into the cell which holds the dates and just press enter, this particular row will show up. Therefore, if there are 8 dates for a product and I go into each date and press enter, all 8 dates will show up on the new tab.


Clearly there is something wrong with the date but I am not sure what. It is formatted in the same way. It is in the same cell format.

If I then reapply the formatting macro, I have to go into each of the cells and press enter again, but only on certain products - It's truly baffling!

 

[steve the fish]

Id imagine its when you trim it. It will become textual and excel converts it when you reenter the cell.

 

[RockandGrohl]

Id imagine its when you trim it. It will become textual and excel converts it when you reenter the cell.

My question is why does it still act as a date higher up in the sheet? I'm converting all rows in the column to dates, or rather, reapplying the formatting.

Oh and, I moved the date conversion to the bottom of the macro so it applies last and this still doesn't work.

 

[RoryA]

At a guess the ones that don't work have a day part that's higher than 13, so they are not converted. The others are probably having the day/month swapped but you haven't noticed yet.

 

[steve the fish]

Of course i have no idea what you mean when you say still act as a date higher up the sheet. I cant see it dont forget. Try using ISTEXT on the dates. What do they return? You dont do any conversions when you apply formatting. You just change the look.

 

[RockandGrohl]

Of course i have no idea what you mean when you say still act as a date higher up the sheet. I cant see it dont forget. Try using ISTEXT on the dates. What do they return? You dont do any conversions when you apply formatting. You just change the look.

Sorry bud, wasn't being sarcastic, wondering out loud!

I will check Rory's suggestion and see if I haven't noticed anything fishy. Then I'll have a look at using ISTEXT, that's a good shout.

Yep, you're both right - the formatting has swapped the day/month around and converted the dates to text. I need to re-assess the trim function to only trim stuff that isn't a date. Thanks both.