Re: Date format issue

Tech-Archive recommends: Fix windows errors by optimizing your registry

On Wed, 6 Dec 2006 12:20:01 -0800, CHWRoma wrote:

I knew I wasn't being clear. I'm not sure how to explain the issue. The
data comes from an outside source in excel format. When I get it the date
format is numbers (i.e. 45488). When I format it in excel it will show as
7/15/24 but it is actually 7/15/2024. Then when I import to Access it comes
in as 7/15/2024. I need to be able to change it to 7/15/1924.

Thanks again,
CHW.Roma

"CHWRoma" wrote:

I am not sure this is the discussion group I need but I don't see any other
that fits.

I have an excel file that has the DOB date coming in as 1/1/2055, instead of
1/1/1955. I can fix it in excel but when I bring it into Access it still
exists the old way. Is it possible to write something using our system date
"Now()" to change it back to the correct format?

Thank you,
CHW.Roma

Well, you're still not being clear.

Note:
Since Year2K, Access and Excel assume any date entered with a 2 digit
year of 00 to 29 will be a 21st century year, i.e. 2029. Any 2 digit
year entered from 30 to 99 will be assumed to be a 20th century year,
i.e. 1999.
If this is creating a problem for you (and it seems to be) do NOT use
a 2 digit year for data entry. Enter dates as 7/24/1924 so Access and
Excel don't have to assume anything.

In Access, as well as in Excel, the value of 45488 as a Date value is
7/15/2024.
If you wish to change that value then run an update query after you
import the data. Make sure the field's datatype is Date/Time.

Update YourTable Set YourTable.DateField =
DateAdd("yyyy"-100,[DateField])

For example, the value of 45488 will then be stored as 8963 (the Date
value of 7/15/1924).

The above will change the VALUE of ALL records in that DateField.
If you wish only certain records changed, then add a where clause to
the above. For example:

Where [DateField]> Date()

will change only those records that are in the future.

If you simply wish to change how the field displays the date, (as
7/15/24), write:
m/d/yy
in the field's Format property line. But that just changes what you
see, the underlying value is still 7/15/2024.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
.

Relevant Pages

  • RE: Excel & MS Query caused compile errors
    ... My data is in an Excel file called ... The format of it were distorted after copying and pasting. ... Microsoft SQL Server 2000, which requires a server, VBA or Microsoft Query ...
    (microsoft.public.excel.programming)
  • RE: Export to Excel: 2nd, 3rd, etc excel files not opening up
    ... early bindings to use the built-in functions of excel to format my excel ... Well based on your suggestions my understanding is that in my early binding ... Set xlApp = New Excel.Application ... I also was able to format that same exported excel ...
    (microsoft.public.access.formscoding)
  • RE: Get parameter dates in heading
    ... And if it goes in Excel where do you put it. ... Const conLightGray As Long = 12632256 ... Dim varGetFileName As Variant 'File Name with Full Path ... Dim strLeftRange As String 'Used to format range references ...
    (microsoft.public.access.externaldata)
  • Re: Dragging data to Excel using DoDragDrop
    ... It just puts a "biff8" object into the clipboard, ... object into Excel. ... if you can use a native Excel data format from ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Text formatted cells displaying numbers in scientific format
    ... If Excel is, indeed, 'trying to be helpful', it's not doing a good job ... distinguishes the data in question from any other numeric string, ... has the cell display the value in line with it's format (i.e. ...
    (microsoft.public.excel)