Convert text field to date format (1 Viewer)

doobybug1 · May 18, 2017

Hi,

I have imported an excel sheet in a table, with one of the fields with dates in the format such as 19th December 1803. Apparently, I cannot use any functions in excel to format the date as a date field instead of a text field in excel. Is there a way on access?

Ranman256 · May 18, 2017

i dont think it can in that format. The import would need to break up the field DD, MONTH YYYY, then post it to a date field mm/dd/yyyy

left(field,2) as day
right(field,4) as year
mid(field,6, instrRev(field," ")

then once imported into these 3 field, a table of months can convert the string back to number ,and post to the date field.

isladogs · May 18, 2017

I'm sure you can do this in Excel & certainly you can do it in Access.

However dates before 30th Dec 1899 cannot be used in date difference calculations as that was day zero for MS Office programs

Assuming you are not back in 1803 then:
CDate(#30 December 1939#) gives an output = 30/12/1939 (in UK date format) or 12/30/1939 for US date format

NOTE:
1. enclose the dates using #
2. You need to remove the 'th' after 30 etc ; 1st =>1; 2nd =>2 etc

doobybug1 · May 18, 2017

Unfortunately all the dates in my excel sheet are pre 1900...most of them are in the beginning of 1800

isladogs · May 18, 2017

doobybug1 said:
Unfortunately all the dates in my excel sheet are pre 1900...most of them are in the beginning of 1800

Family history database?

You can convert them OK
e.g. CDate(#30 December 1803#) gives an output = 30/12/1803

Each day is assigned a number where 30/12/1899 is day 0
CLng(#31/12/1899#)=1
CLng(#29/12/1899#)=-1

and I'm sure you want to know that
CLng(#18/05/2017#) = 42873
CLng(#31/12/1803#) = -35063 etc ....

However, calculations MAY not always work correctly
e.g. number of days between 2 dates

Having said that I've just tried the following:
DateDiff("d",#28/12/1945#,#31/12/1946#)
DateDiff("d",#28/12/1845#,#31/12/1846#)

Both give the answer 368

So my advice is try it ... BUT check calculations of age etc

doobybug1 · May 18, 2017

Thanks so much...so those commands I assume I should insert them in VBA code. I am working on an archiving database, that is why I have dates that are so old

isladogs · May 18, 2017

Yes - use in VBA code or in some cases something similar will work in queries

Galaxiom · May 19, 2017

ridders said:
However dates before 30th Dec 1899 cannot be used in date difference calculations as that was day zero for MS Office programs

Incorrect. Access works fine with dates that are before zero day.

The earliest date Access can handle is 1/1/100.

isladogs · May 19, 2017

Galaxiom said:
Incorrect. Access works fine with dates that are before zero day.

The earliest date Access can handle is 1/1/100.

I stand corrected....

What's the significance of / reason for ... that particular date?

The_Doc_Man · May 20, 2017

You might as well ask the significance of the UNIX epoch date or the OpenVMS epoch date. In each case, it is simply a date that the designers agreed to use.

Whether we are talking 1/1/1900 (Windows) or 1/1/1970 (Unix) or 17-Nov-1858 (OpenVMS), it was a date that made sense at the time.

Galaxiom · May 20, 2017

The significance of the 1/1/100 date is that year 99 is interpreted as 1999.

isladogs · May 21, 2017

Galaxiom said:
Incorrect. Access works fine with dates that are before zero day.

The earliest date Access can handle is 1/1/100.

Galaxiom said:
The significance of the 1/1/100 date is that year 99 is interpreted as 1999.

17 years on, its the millennium bug .... deja vu all over again

Convert text field to date format (1 Viewer)

doobybug1

Registered User.

Ranman256

Well-known member

isladogs

MVP / VIP

doobybug1

Registered User.

isladogs

MVP / VIP

doobybug1

Registered User.

isladogs

MVP / VIP

Galaxiom

Super Moderator

isladogs

MVP / VIP

The_Doc_Man

Immoderate Moderator

Galaxiom

Super Moderator

isladogs

MVP / VIP

Similar threads

Users who are viewing this thread