Date Formatting

S

SageOne

I have a spreadsheet with the field name "Date Received". Currently, the
field is formatted as text. Because of user input error, some of the record
values for the field are random text strings like "jjdjdk", "iiebne*01" and
"444456". For the rest of the record set the dates have been entered
correctly in the format "mm/dd/yyyy".

I would like to covert this field to date/time format. I would also like to
populate anything that isn't in the "mm/dd/yyyy" format as the default date
"01/01/2999".

how can I identify the random text strings and replace them with a default
date?
 
K

KARL DEWEY

I have a spreadsheet with the field name "Date Received".
You use the term 'spreadsheet' which applies to Excel and not to Access but
I will assume you made a mistake and go from there.
You have to create a new field with DataType as DateTime.
Then use this to update the new field --
IIf(IsDate([Date Received]),CVDate([Date Received]),#01/01/2999#)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top