Thread: can Excel format dates like Access?

I am used to Access, where you set an input mask for dates, making it unnecessary to type in the slashes. So, if you type 10192011 in Access, you get 10/19/2011. If I try the former in Excel, I just get #######--and no, I made sure the column cells are formatted for dates and that the column is wide enough. I work in a big epidemiological research institution with multiple data bases and massive amounts of data entry, so it would save us data entry people a lot of time if we didn't have to type in the date slashes. Thanks, Janie Quick

Excel displays dates as a "trick". The actual value IN the cell is a long number, currently in the 40800 range. But it goes up one number per day. So, 10192011 Excel will interpret as date beyond the year 9999, which is where the error is coming from.

One built in non-VBA way to force them to enter the dates properly is through Data Validation. If the column B, then highlight B2:B100 and apply the Data Validation settings of:

Allow: Custom
Formula: =B2<50000

You can play with that, but that should certainly work for most entries, especially if the cell is formatted for DATE and thus converting most entries into the closest logical date value.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.