how do i convert a number to a date

[Guest]

I have dates 20060605 and need to convert it to 05/06/2006 - can any one help

 

[Allen Browne]

Assuming your field is named "d" and always contains 8 digits, this will
give you a date/time field:

DateSerial(Left([d], 4], Mid([d],5,2), Right([d],2))

 

[John Spencer]

ONE method to convert to an actual date time field is

IIF(IsDate(Format([YourField],"@@@@-@@-@@")),CDate(Format([YourField],"@@@@-@@-@@")),Null)

 

[Guest]

If I have a text field that stores a date and time as text, how do I convert
it so I can use that field as a date and time to be able to do a datediff
function on it. My data in the field looks like this, 200705031400 and I
need it to show as 2007/05/03 14:00 or something similar. Thanks

Assuming your field is named "d" and always contains 8 digits, this will
give you a date/time field:

DateSerial(Left([d], 4], Mid([d],5,2), Right([d],2))

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have dates 20060605 and need to convert it to 05/06/2006 - can any one
help

 

[Jeff Boyce]

Take a look at the DateSerial() function, and the Left(), Mid() and Right()
functions. There may be a TimeSerial() function available, too.

Regards

Jeff Boyce
Microsoft Office/Access MVP

If I have a text field that stores a date and time as text, how do I
convert
it so I can use that field as a date and time to be able to do a datediff
function on it. My data in the field looks like this, 200705031400 and I
need it to show as 2007/05/03 14:00 or something similar. Thanks

Assuming your field is named "d" and always contains 8 digits, this will
give you a date/time field:

DateSerial(Left([d], 4], Mid([d],5,2), Right([d],2))

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have dates 20060605 and need to convert it to 05/06/2006 - can any one
help

 

[Guest]

This does not work, I always get a null value.

ONE method to convert to an actual date time field is

IIF(IsDate(Format([YourField],"@@@@-@@-@@")),CDate(Format([YourField],"@@@@-@@-@@")),Null)

I have dates 20060605 and need to convert it to 05/06/2006 - can any one
help

 

[Jeff Boyce]

Sean

I can't tell what you are referring to when you say "This does not work".

Did you try the DateSerial() function and the other ones I suggested?

Regards

Jeff Boyce
Microsoft Office/Access MVP

This does not work, I always get a null value.

ONE method to convert to an actual date time field is

IIF(IsDate(Format([YourField],"@@@@-@@-@@")),CDate(Format([YourField],"@@@@-@@-@@")),Null)

I have dates 20060605 and need to convert it to 05/06/2006 - can any one
help

 

[John W. Vinson]

If I have a text field that stores a date and time as text, how do I convert
it so I can use that field as a date and time to be able to do a datediff
function on it. My data in the field looks like this, 200705031400 and I
need it to show as 2007/05/03 14:00 or something similar. Thanks

Try:

DateSerial(Left([yourdate], 4), Mid([yourdate], 5, 2), Mid([yourdate], 7, 2))
+ TimeSerial(Mid([yourdate], 9, 2), Mid([yourdate], 11, 2), 0)

if the other suggestions continue to give you trouble.

John W. Vinson [MVP]

 

[Guest]

When I run the query and use:
IIF(IsDate(Format([YourField],"@@@@-@@-@@")),CDate(Format([YourField],"@@@@-@@-@@")),Null)
I always get a null value, it does not convert the date.

The dateserial works but it only gets me the date, I need the time to show
as well becuase I am going to compare two different date times for the same
item and I need to see the difference between them in hours. Any thoughts?

Thanks again,
Sean

Sean

I can't tell what you are referring to when you say "This does not work".

Did you try the DateSerial() function and the other ones I suggested?

Regards

Jeff Boyce
Microsoft Office/Access MVP

This does not work, I always get a null value.

ONE method to convert to an actual date time field is

IIF(IsDate(Format([YourField],"@@@@-@@-@@")),CDate(Format([YourField],"@@@@-@@-@@")),Null)




I have dates 20060605 and need to convert it to 05/06/2006 - can any one
help

 

[Jeff Boyce]

Sean

Our postings seem to be missing each other.

John has given you an example of what I was describing in my original
response - the use of the DateSerial() and TimeSerial() functions.

Regards

Jeff Boyce
Microsoft Office/Access MVP

When I run the query and use:
IIF(IsDate(Format([YourField],"@@@@-@@-@@")),CDate(Format([YourField],"@@@@-@@-@@")),Null)
I always get a null value, it does not convert the date.

The dateserial works but it only gets me the date, I need the time to show
as well becuase I am going to compare two different date times for the
same
item and I need to see the difference between them in hours. Any
thoughts?

Thanks again,
Sean

Sean

I can't tell what you are referring to when you say "This does not work".

Did you try the DateSerial() function and the other ones I suggested?

Regards

Jeff Boyce
Microsoft Office/Access MVP

This does not work, I always get a null value.

:

ONE method to convert to an actual date time field is

IIF(IsDate(Format([YourField],"@@@@-@@-@@")),CDate(Format([YourField],"@@@@-@@-@@")),Null)




I have dates 20060605 and need to convert it to 05/06/2006 - can any
one
help

 

[Guest]

I tried that but I was unable to get the TIME portion to appear. Only a date
was returned 05/04/2007 but the time part, 12:00 was not included in the
results. This is where I am having issues. Any thoughts?

Sean

Our postings seem to be missing each other.

John has given you an example of what I was describing in my original
response - the use of the DateSerial() and TimeSerial() functions.

Regards

Jeff Boyce
Microsoft Office/Access MVP

When I run the query and use:
IIF(IsDate(Format([YourField],"@@@@-@@-@@")),CDate(Format([YourField],"@@@@-@@-@@")),Null)
I always get a null value, it does not convert the date.

The dateserial works but it only gets me the date, I need the time to show
as well becuase I am going to compare two different date times for the
same
item and I need to see the difference between them in hours. Any
thoughts?

Thanks again,
Sean

Sean

I can't tell what you are referring to when you say "This does not work".

Did you try the DateSerial() function and the other ones I suggested?

Regards

Jeff Boyce
Microsoft Office/Access MVP

This does not work, I always get a null value.

:

ONE method to convert to an actual date time field is

IIF(IsDate(Format([YourField],"@@@@-@@-@@")),CDate(Format([YourField],"@@@@-@@-@@")),Null)




I have dates 20060605 and need to convert it to 05/06/2006 - can any
one
help

 

[John W. Vinson]

I tried that but I was unable to get the TIME portion to appear. Only a date
was returned 05/04/2007 but the time part, 12:00 was not included in the
results. This is where I am having issues. Any thoughts?

A Date/Time field is actually stored as a double float number, with the
integer portion being the number of days since midnight, December 30, 1899;
and the fractional portion being in fractions of a day. By default, if the
fractional portion is 0 - midnight - only the date is displayed. you can
override this by setting a Format such as Long Date or mm/dd/yyyy hh:nn to
explicitly force the time to be shown. Times other than midnight should show
up though, unless you specify a date-only format such as Short Date.

John W. Vinson [MVP]

 

[Jeff Boyce]

Sean

As John points out, you may be storing one thing, but seeing something else.
Take a look at how your field is being formatted.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I tried that but I was unable to get the TIME portion to appear. Only a
date
was returned 05/04/2007 but the time part, 12:00 was not included in the
results. This is where I am having issues. Any thoughts?

Sean

Our postings seem to be missing each other.

John has given you an example of what I was describing in my original
response - the use of the DateSerial() and TimeSerial() functions.

Regards

Jeff Boyce
Microsoft Office/Access MVP

When I run the query and use:
IIF(IsDate(Format([YourField],"@@@@-@@-@@")),CDate(Format([YourField],"@@@@-@@-@@")),Null)
I always get a null value, it does not convert the date.

The dateserial works but it only gets me the date, I need the time to
show
as well becuase I am going to compare two different date times for the
same
item and I need to see the difference between them in hours. Any
thoughts?

Thanks again,
Sean

:

Sean

I can't tell what you are referring to when you say "This does not
work".

Did you try the DateSerial() function and the other ones I suggested?

Regards

Jeff Boyce
Microsoft Office/Access MVP

This does not work, I always get a null value.

:

ONE method to convert to an actual date time field is

IIF(IsDate(Format([YourField],"@@@@-@@-@@")),CDate(Format([YourField],"@@@@-@@-@@")),Null)




I have dates 20060605 and need to convert it to 05/06/2006 - can
any
one
help