Date calculations

[NOTLguy]

I am trying to convert date data from Excell in the "days from 1900" format to (mm/dd/yyyy) in Access. Is there a function or procedure available for this?

Any help would be appreciated
Regards,
Bill

[NoellaG]

Normally access and Excel have the same approach to date formats. Applying a format will show the dates as you want to see them, both in Access and in Excel.

grNG

[NOTLguy]

Normally access and Excel have the same approach to date formats. Applying a format will show the dates as you want to see them, both in Access and in Excel.

grNG

Thanks for your reply. I probably didn't explain myself very well. The data was imported from an Excell spreadsheet and the dates came over as numbers ( eg. 34887 ). I want to convert this data to a standard date format in the Access table (mm/dd/yyyy). Or convert it to date format in Excell and then import into an Access table. Thus the question as to how to convert the numbers to date format.

Regards,
Bill

[ajetrumpet]

I am trying to convert date data from Excell in the "days from 1900" format to (mm/dd/yyyy) in Access.

if you're sure that the data came over from excel such that they are "that many days after January 1, 1900", run an UPDATE query on your table and update the DATE field to this:

[datefield] = dateadd("d", [datefield], #1/1/1900#)

it's as simple as that really. however, it may not work if the access datefield is DATE/TIME data type already. make sure to check that first.

backup the table before doing it!

[slave138]

You might want to verify the date in Excel is from 1/1/1900. From what I've read, Excel starts at 12/31/1899 so Day #1 is 1/1/1900. There is also an intentional bug in Excel where 2/28/1900 is day 59 but 3/1/1900 is day 61. There wasn't a leap year in 1900 so there is no Day #60.

I think you might want to use CDate([ExcelDate]) to convert the number to a date in Access instead of the dateadd method because it accounts for these quirks.

[NOTLguy]

You might want to verify the date in Excel is from 1/1/1900. From what I've read, Excel starts at 12/31/1899 so Day #1 is 1/1/1900. There is also an intentional bug in Excel where 2/28/1900 is day 59 but 3/1/1900 is day 61. There wasn't a leap year in 1900 so there is no Day #60.

I think you might want to use CDate([ExcelDate]) to convert the number to a date in Access instead of the dateadd method because it accounts for these quirks.

*********************************

I ran an update query using CDate([bdate]), bdate being the field that the dates from excel are stored ( example: 38947 ). The field is a text field. Nothing happened. Is this because it is a text field?

Thanks for everyone's help as I am an amateur at this.

Bill

[slave138]

You might just need to convert the text into a number first... Try this:
CDate(CLng([bdate]))

[NOTLguy]

I ran the update query again and still no change to the data, except that the field type is now 'number'. I am using Access 2002. Is it possible that the Access version is too old and doesn't recognize CDate()?

Update query looks like this:
CDate(CLng([Bdate]))

Data looks like this after running the update query:

36177
36189
36209

Regards,
Bill

[slave138]

CDate has been available in Access for quite awhile so I don't think it's a version problem. I don't suppose you'd care to upload a copy of the files (with everything but the dates cleared)?

As an FYI these are the dates I got for the numbers you posted:
36177 = 1/17/1999
36189 = 1/29/1999
36209 = 2/18/1999

[NoellaG]

Hi,

you can't change a text field to a real date: first create a new column in your table, date type: Date/Time. Then fill up that field with an update query using Cdate. If all went well you can delete the original column.
Hope this works for you.

grNG

[NOTLguy]

Hi,

you can't change a text field to a real date: first create a new column in your table, date type: Date/Time. Then fill up that field with an update query using Cdate. If all went well you can delete the original column.
Hope this works for you.

grNG

That worked NoellaG! Thank you so much for your help on this.

Sincerely,
Bill