Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2006
    Posts
    103

    Unanswered: system date format problem

    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

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    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

  3. #3
    Join Date
    Jun 2006
    Posts
    103
    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.

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •