Convert 20020322 number into Date Format

[indy1982]

Hi All,

I have two columns in Access, Entry_Date & Value_Date.

When I import this data into Access via a text file it is recognised as a number format ie 20020322. I want to show this as date format in the easiest possible way.

I want to be able to show both these columns as date format ie, 20020322 as either 22-03-2002 or 22/03/2002. I then want to be able to work out the number of days between both dates eg Value_Date minus Entry_Date for example.

Ideally I would like to update the columns and not have to insert any additional columns.

Can anybody tell me how this is possible? Thanks

Indy

 

[JANR]

lookup DateSerial(Year, Month,Day) function

x=20020322

DateSerial(Left(x,4), Mid(x,5,2),Right(x,2))

JR

 

[indy1982]

Hi JR,

Thanks for this, I have basic knowledge of Access.

How can I use the function you have given me below to update both Entry_Date and Value_Date columns without creating new columns.

So 20020322 would show as 22/03/2002 in date format for all fields in the Entry_Date column for example. I just want to update those columns.
Thanks.

Indy

 

[JANR]

If your Entry_Date and Value_Date is of the datatype Number then you can run this query:

UPDATE MyTable SET MyTable.Entry_Date = DateSerial(Left([entry_date],4),Mid([entry_date],5,2),Right([entry_date],2)), MyTable.Value_Date = DateSerial(Left([value_date],4),Mid([value_date],5,2),Right([value_date],2));

Just match the boldpart with your tablename.

Now after you have updated your table, change the datatype off those two fields to Date/Time and you should be set.

It goes without saying TEST ON A COPY FIRST!!!

Edit:
It should work on datatype Text or Number


JR

 

[Alansidman]

Crossposting: http://www.mrexcel.com/forum/showthread.php?t=609953

 

[indy1982]

Hi JANR,

The answer below posted on 02-02-2012 03:09 PM is great and works fine.

Is it possible now to work out the number of Business Days between Value_Date and Entry_Date and have the answer populate another column?

Apologies for the cross-posting I am quite new to these forums.

Thanks

Indy

 

[JANR]

Is it possible now to work out the number of Business Days between Value_Date and Entry_Date and have the answer populate another column?

Technically anything is possible, BUT this something that you calculate on demand and not store in the table.

As for caluclating workdays that's not easy since you have to test both weekends and holliday's, weekend isen't to difficult but you have to use an Array or a recordset with holliday dates to test against. Excel has a function called Networkdays but Access doesen't have it so you must create it yourself, something like this here: http://access.mvps.org/access/datetime/date0012.htm

JR