Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Jul 2006
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    2003 (Date formatting)

    Hi, I have an excel spreadsheet in which a date field has been completed as text e.g. "01.01.2005". I am trying to update this to date format "dd/mm/yyyy" - this works for some fields, but the majority return a value of 30/12/1899 - can anyone explain why and if I can do anything about it?

    thanks for your help!

    Ina

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 2003 (Date formatting)

    Are you trying to modify the Excel spreadsheet from Access? Is there a reason not to do it in Excel itself?

  3. #3
    Star Lounger
    Join Date
    Jul 2006
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 2003 (Date formatting)

    There are formatting issues all over the source spreadsheet which has not been consistent in any field and has thrown out loads of errors. In order to import the data at all, everything had to be reformatted as text. in the source and we're working on it in Access. As I'm working with 130,000 records across 6 spreadsheets, and i was hoping for a quick fix! I'm also just curious regarding why this formatting change doesn't work ...

    Ina

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 2003 (Date formatting)

    To avoid misunderstanding, please explain clearly whether you want to fix an Excel spreadsheet, or an Access table that resulted from importing an Excel spreadsheet into Access.

  5. #5
    Star Lounger
    Join Date
    Jul 2006
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 2003 (Date formatting)

    The source spreadsheet is in Excel - it has been imported into Access. I wish to format the date field within access. Thanks.


    Ina

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 2003 (Date formatting)

    So you have a text field in an Access table. Setting a date format won't turn it into a valid date field.

    If you only need to view the dates, you can create a query based on the table, and add a calculated field that returns the date corresponding to the text value.

    If you need to be able to edit the dates, you will have to add a date/time field to the table, and use an update query to populate the date/time field. After this has been done, you can discard the text field.

    Which option do you prefer?

  7. #7
    Star Lounger
    Join Date
    Jul 2006
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 2003 (Date formatting)

    Hi Hans, I do need the date time field, which I've put in, but as I've got something like 4,000 distinct dates for which I'd need to type in a correlate in either a lookup table or update query, I was hoping to find a quicker route. Sounds like there isn't one?

    Ina

  8. #8
    Star Lounger
    Join Date
    Jul 2006
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 2003 (Date formatting)

    Hi Hans, I have got round this by using Date: Mid([PODate],1,2) & "/" & Mid([PODate],4,2) & "/" & Mid([PODate],7,2) which seems to then allow me to change it to date format without having to use update tables. thanks for your help though.

    Ina

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 2003 (Date formatting)

    That's basically the first of the two possibilities I mentioned. Glad you were able to solve it yourself.

    Note: your "Date" will still be a text field. If you need it to be a real date that you can use for calculations, use

    Date: DateValue(Mid([PODate],1,2) & "/" & Mid([PODate],4,2) & "/" & Mid([PODate],7,2))

Posting Permissions

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