Possible IF code for Date ??

[ipbr21054]

Hi,
For some time now & still unable to find the cause im asking if there is a vba code etc for which will check & alter the date.

The worksheet is called DATABASE
The date will always be in column N
The first row this applies to is Row 5

My issue is sometimes i paste or typr the day like so into the cell 09/02/2023
For some reason its changed to 02/09/2023

Its not always noticed at first but then sometime down the road when checking a customers file then its noticed.
Example.
My database shows the part being out of warranty but when the customer shows me the receipt the date is different.
Looking on the databse then confirms he is correct & my date in the column is wrong.

Hence why a code that if the date in column N is MM/DD/YYY then change it to DD/MM/YYY

Thanks please advise

 

[Micron]

Dates are stored in US date format regardless of what you enter (at least that is the case in Access). I have to imagine that there is a conflict between Regional Settings and cell formats at one or maybe even at both ends. Did you check cell formatting to see if the reason lies there?

 

[awoohaw]

@Micron , I do not think he has. I asked him to go through that little exercise I posted in one of the earlier threads. Im guessing he has not since I see no mini worksheet posted by him. I also do not know if he has general format or a date format. It's up to him to provide us with more information to help. But, I think he is in the US, and maybe he is working with someone with dd/mm/yyyy as their regional setting.

But, excel stores the date as a serial number, not a formatted number, right? It is the regional setting that displays for a general format cell, right? And, if a user formats the cell, even for import/paste shouldn't it display correctly?

Another thought, what if a dd/mm/yyyy date as text was pasted into a document, wouldn't it think it is mm/dd/yyyy?

 

[Micron]

But, excel stores the date as a serial number, not a formatted number, right?

I would expect that Excel and Access store a date value in the same way - double precision floating point numbers. Not sure what you mean by "serial" number.
Maybe wrt Excel, this or a similar site might help shed some light

Everything you need to know about working with dates and time in Excel

One of the most challenging aspects of Excel is working with dates and times. It's certainly one of the most common topics I get asked about. Download my comprehensive Excel workbook and PDF that covers EVERYTHING you need to know about working with dates and time in Excel.

www.myonlinetraininghub.com

Yes, regional settings of dd/mm/yy decide that if entering 02/05/2023 you want May 02 2023. However, I'm thinking that cell formatting can mess up your intentions if they don't reflect regional settings. AFAIK, 05/02/2023 (May 02 2023) cell value may be just that, but cell formatting could switch it to February 05. Fortunately I don't have to worry about this issue because I use US format and don't develop for users who don't so I don't think I have a lot more to offer solution wise.

 

[awoohaw]

Yep, I'm in the same boat. Hopefully someone that uses his regional settings that deal with users with different regional settings can tell him best practices.

 

[ipbr21054]

Just to clear some things .
My database if where i checked the date.
Customer had shown me paper receipt with different date.
I am in UK

Regional screenshots shown.
I was unable to continue last night due to UK time.

In row 6 at cell N6 i type 11/02/2023 the formula bar also shows 11/02/2023
I leave the cell & i see the date change to 02/11/2023
I click in the date cell again & formula bar shows 02/11/2023

My first empty row is at Row 410
So in the cell i type 11/02/2023 & formula bar also shows 11/02/2023 so i then leave the cell
I see the date in the cell change to 11/2/2023 month 0 now removed
Formula bar show 02/11/2023


In the cell i type 02/11/2023 formula is the same,i leave cell and celnow shows 2/11/2023 & formula bar shows 11/02/2023

Typing a higher date like 28/12/2023 now changes are made at all cell or formula bar

Does this help

 

[Micron]

Different results in different cells suggests you have a cell formatting issue.

 

[ipbr21054]

Different results in different cells suggests you have a cell formatting issue.

I agree with you there.

I see no date related items in conditional formatting.

Right clicking on the worksheet tab & going through all the code I see no date related code.

So I assume it must be something in one of the many modules ?

Is there something I can do to be told which module the code for the date is in or must I manually look / go though each module to find it.

Thanks.

 

[awoohaw]

please do the little drill I asked for in Post #2? And post that result. Do not just write about the result, show us the result.

 

[ipbr21054]

Unable to find yet where the formatting is stoed so i continued

I typed 9/2/2023 & when i left the cell it stayed the same BUT formula bar shows 02/09/2023

I typed 2 Sept 2023 & when i left the cell it changed to 2/9/2023 & formula bar shows 09/02/2023

I typed Sept 2, 2023 & when i left the cell it changed to 9/2/2023 & the formula bar shows 02/09/2023

 

[Micron]

I see no date related items in conditional formatting.

I was referring only to cell formatting - what you get if you press ctrl+1 and look at number tab