Calendar dates are represented in a lot of different short forms.

[zoharb]

I have faced a real problem in this-has inserted data+time stamp suing Ctrl+: and ctrl+Shift+" . Now if keeps the space , it get recognized as date and NOW(), and DATEDIFF like date functions work but else it is considered as TEXT and nothing works.
Do you have some thought on this so that I could add date+timetamp coming from UK and USA in same sheet and formulas(DATE) work correctly

e	Date	Discription	Po#	File Count	Order Placed By	Order Type	Stitches	Amount	Status	Company Name
NEW	01/10/2025 06:46	AE9F3EF8	SUMMIT AIR CONDITIONING		Mandy	artwork		5.95		Signarama Hindmarsh			NZD
541	01/10/2025 06:46	Jovonna-3xredraws	3xredraws		Neena	artwork		GBP 3.20		FASTSIGNS 872 of Hammersmith			UK
317	01/10/2025 08:17	Four Little Birds			Jeff Sawell	ARTWORK		5.95		Speedy Signs Takapuna			AUS
350	01/10/2025 10:33	ALLstyle			Karien Greyvenstein	artwork		5.95		Sign-A-Rama Joondalup			AUS
83	01/10/2025 10:47	CTS		LEOMA-PXF	Leome	digitizing	6040	12.02		art.chermside@fullypromoted.com.au	EmbroidMe of Chermside		USA

• In some countries, 12/10/20 (DD/MM/YY) stands for October 12, 2020.
• In other countries, the national standard is YYYY-MM-DD so October 12, 2020 becomes 2020-10-12.
• In the United States, (MM/DD/YY) is the accepted format so October 12, 2020 is going to be 10/12/20.

Now, think about what would happen if you were working global company and didn’t check date formats. Well, your data integrity would probably be questionable. Any analysis of the data would be inaccurate. Imagine ordering extra inventory for December when it was actually needed in October!

A good analysis depends on the integrity of the data, and data integrity usually depends on using a common format. So it is important to double-check how dates are formatted to make sure what you think is December 10, 2020 isn’t really October 12, 2020, and vice versa.

 

[Alex Blakenburg]

Are you able to break the fields out into a date field and a time field ?
If you do that then you can select the Date format that is prefixed with "*". This will adapt the format to the user's Region Setting for the date format.

 

[zoharb]

Respected Sir Alex,
Okay. FOr that I have to rearrange the sheet and write all the formulas related to that again . Its fine as it will handle all the future entries correctly
My system date is in mm/dd/yy(american date format) . we mostly fetch date+time stamp directly from email of sender.
Is there anyway that date mentioned in dd/mm/yy or dd-mm-yyyy get fetcted as mm/dd/yy(eg TEXT function
like
=DATE(RIGHT(B5,2)+2000,MID(B5,4,2),LEFT(B5,2))
OR
=DATE(RIGHT(TRIM(A1),2)+2000,MID(TRIM(A1),4,2),LEFT(TRIM(A1),2))--trim extra spaces

AND SAME THINGS HAPPENED WHERE TIME IS MENTION IN 12 HRS FORMAT AS FORMULAS NEED 24 HRS FORMAT

I had tries this solutions but the employees at data entry level do this mistakes and that creates problem at analysis level.
Can we make such that just at time of extraction , correct data is inserted in database

 

[Alex Blakenburg]

we mostly fetch date+time stamp directly from email of sender.

You might not need to split date and time. Try using this number format for your date column and test it out on someone with a different Region Date Time setting.

Columns("B").NumberFormat = "m/d/yyyy h:mm"

Using that setting if they copy the date and time from their email it should handle it correctly.

Checking whether historical data has been correctly interpreted is problematic.
The issue is as you identified in the original post how do you tell whether 12/10/2020 was meant to be 12 Oct or 10 Dec. The actual conversion once identified is not too difficult.
It would be easier if they submit a spreadsheet with only their data in it and for a whole month at a time. You could then check if dates with days > 12 are appearing as text and then make the assumption that all their dates have dd and mm reversed. Having said that if they submit one month at a time you would already know what the month should be. If they are all inputting into a shared spreadsheet it would be very difficult to work out.