Hi All,

I have a problem on dealing with the date format in excel,

I tried to use a date which from combo box, then i try to validate it if the input is a future date, my code validate the dates like this

dim dateCurrent As Date
dateCurrent=DateValue(txtDay.text & "/" txtMonth.text & "/" & txtYear.text)

if DateValue(Now()-dateCurrent)<0 then
Msgbox "Future Date detected!"
exit sub
end if

the problem occur when some of the pc in my company has the date format which has dd/mm/yy format, but some has mm/dd/yy, then my function has a problem on handle both cases, i can solve the problem by swapping the day and the month

Question: is there a way to know what date format is the pc is using so the program can decide which piece of code must be used?

Another way is to change the date format to my preference, but then i dont have the admin right to do this!!!

Any bright idea, anyone?

Thanks

Hi mkggoh

Have you tried using the DateSerial() function in place of DateValue()

Help gives you all you need on this.

Just an idea.

Let us know.

Why are some PCs on UK and some on continental date format? Are they in different countries?

MTB

hm....i dont think DateSerial will work, i read the detail in the web

DateSerial (2004, 6, 30) 'would return would return '6/30/2004'

but the problem is i dont know how to get the date format from the system,

if the system date format is mm/dd/yy, then DateSerial (2004, 6, 30) is valid
else it is an invalid date, because it will treat 30 as the month.

just like the dateValue function,
dateCurrent=DateValue(txtMonth.text & "/" txtDay.text & "/" & txtYear.text)

if the system date format is mm/dd/yy, the function work fine, if the system date format is dd/mm/yy, then this function will give a wrong date.

Is that a way to detect the system date format (since it might be difficult for me to change all 400 pc in my company to fit the format i want, and i need to get an admin rights), if i can detect the date format, then i can use an if/else to do this job

i m sure thats a way to detect the system date format, since my excel template has no problem on inserting records into access database regardless of the date format, i bet there must be a protocol between the excel and access in order to do that, they never mess up the format of the date.

Thanks.

Hi again

I don't think DateSerial will interperate DateSerial (2004, 6, 30) as 30 being the month (it can not as ther are ony 12 months !), as the function definition is for the last argument to be days !!

Anyway DateSerial takes named arguments so try this

DateSerial(Day:=30, Year:=2004, Month:=06)

which returns 30th June 2004. ???

As fare as I can see the Now() Date() [or Date] and date serial should return a date number that is valid for all machines and, therefore, independent of the machine settings !!??

Then again I don't now what MS have done in there infinite wisdom.

However you are going to run into problems if you use date string in code for queries or filters as these need to formatted as US dates (mm/dd/yy) in all cases, both in Access and Excel.
Being in the UK this is something that catches me out many time (I never learn!).

Does that help at all?

MTB