change number format to date

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I receive a file which has the Date field in a number format as follows:
2032006 (3rd February 2006) or
12082005 (8th February 2005)

I currently use the following formulas to break these dates down and then
reformat them as dd/mm/yyyy
DAY: IIf((Len([DATE])=8), Mid([DATE],3,2), Mid([DATE],2,2))
MONTH: IIf((Len([DATE])=8), Left([DATE],2), Left([DATE],1))
YEAR: Right([DATE],4)
NEW_DATE: [DAY] & "/" & [MONTH] & "/" & [YEAR]

However while the new date field looks like a date, it is not formatted as
one and I am unable to run date and time calculations off it.

Please could you let me know how to change this format?
 
Generate the new date as:
DateSerial([YEAR], [MONTH], [DAY])
substituting each expression for the 3 components.
 
Assumption:
There is always a value in the field

This may work for you, but it may not depending on your locale and system
settings. Test it
CDate(Format([Date],"00/00/0000"))

Safer (more internationally aware)

DateSerial(Right(Format([Date],"00000000"),4),Mid(Format(([Date],"00000000")3,2),Left(Format(([Date],"00000000"),2))

By the way, how does 12082005 translate to Feb 8 2005? I would think it
would be Dec 8, 2005 if I've understood the structure of the field you are
attempting to convert.
 
Thank you very much John (you are right about it being December 8th,
February was a typo).

When I use your method below in a Make-Table Query, I am returned with the
date in the format mm/dd/yyyy instead of dd/mm/yyyy. Changing the format of
the field in the resulting table does not recognise the mm/dd/yyyy format of
the date.

For example using dateserial returns 07/22/2005 (22nd July 2005) however
when I try and change this to a long date format in the resulting table it
brings back 07 October 2006.

Please do you know a way around this?



John Spencer said:
Assumption:
There is always a value in the field

This may work for you, but it may not depending on your locale and system
settings. Test it
CDate(Format([Date],"00/00/0000"))

Safer (more internationally aware)

DateSerial(Right(Format([Date],"00000000"),4),Mid(Format(([Date],"00000000")3,2),Left(Format(([Date],"00000000"),2))

By the way, how does 12082005 translate to Feb 8 2005? I would think it
would be Dec 8, 2005 if I've understood the structure of the field you are
attempting to convert.



Dan said:
Hello,

I receive a file which has the Date field in a number format as follows:
2032006 (3rd February 2006) or
12082005 (8th February 2005)

I currently use the following formulas to break these dates down and then
reformat them as dd/mm/yyyy
DAY: IIf((Len([DATE])=8), Mid([DATE],3,2), Mid([DATE],2,2))
MONTH: IIf((Len([DATE])=8), Left([DATE],2), Left([DATE],1))
YEAR: Right([DATE],4)
NEW_DATE: [DAY] & "/" & [MONTH] & "/" & [YEAR]

However while the new date field looks like a date, it is not formatted as
one and I am unable to run date and time calculations off it.

Please could you let me know how to change this format?
Click to expand...
Click to expand...
 
Sorry, I don't know any way around this as I don't know what is causing
this.

Are you storing the new value you are generating in a DateTime field? You
should be.

Take a look at
International Dates in Access at:
http://allenbrowne.com/ser-36.html


Dan said:
Thank you very much John (you are right about it being December 8th,
February was a typo).

When I use your method below in a Make-Table Query, I am returned with the
date in the format mm/dd/yyyy instead of dd/mm/yyyy. Changing the format
of
the field in the resulting table does not recognise the mm/dd/yyyy format
of
the date.

For example using dateserial returns 07/22/2005 (22nd July 2005) however
when I try and change this to a long date format in the resulting table it
brings back 07 October 2006.

Please do you know a way around this?



John Spencer said:
Assumption:
There is always a value in the field

This may work for you, but it may not depending on your locale and system
settings. Test it
CDate(Format([Date],"00/00/0000"))

Safer (more internationally aware)

DateSerial(Right(Format([Date],"00000000"),4),Mid(Format(([Date],"00000000")3,2),Left(Format(([Date],"00000000"),2))

By the way, how does 12082005 translate to Feb 8 2005? I would think it
would be Dec 8, 2005 if I've understood the structure of the field you
are
attempting to convert.



Dan said:
Hello,

I receive a file which has the Date field in a number format as
follows:
2032006 (3rd February 2006) or
12082005 (8th February 2005)

I currently use the following formulas to break these dates down and
then
reformat them as dd/mm/yyyy
DAY: IIf((Len([DATE])=8), Mid([DATE],3,2), Mid([DATE],2,2))
MONTH: IIf((Len([DATE])=8), Left([DATE],2), Left([DATE],1))
YEAR: Right([DATE],4)
NEW_DATE: [DAY] & "/" & [MONTH] & "/" & [YEAR]

However while the new date field looks like a date, it is not formatted
as
one and I am unable to run date and time calculations off it.

Please could you let me know how to change this format?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top