Allow either a specific date or only the year in a single column

[David Halter]

Hi. I have a situation where most of the time the date to be entered is, at best, known to the year of birth only, but occasionally it is known specifically. Usually this is because the person was born recently, as opposed to 60-70 years ago. Being inaccurate by up to 11 months is not a big deal when the person is 80, but it IS a big deal if the person is only 6 months old! So, my date column would look like this:
1978
1981
1954
1999
02/03/2017
1954
11/12/2017

Question: is there a way to allow this sort of data entry within one column of Excel such that a user can simply enter the known information, either a 4 digit year or else specific date without the user writing about the format? Presume the user in this case is not experienced: or, can find the right columns to enter data, can type, use a mouse, and save the file, but but much else. Of course, there are some solutions: use two columns, one for year, one for specific dates, but I'd like to remove as much confusion as possible!

Thanks for your interest!
Dave

[#2]

David,

Simply, you can format the column as General Format.

But make sure to format only the empty cells of the column, not the whole column because dates will be converted to serial numbers!

With General Format, don't worry about the format, what the user typed will be automatically converted to the correct format.

But I don't recommend you to use this approach!

Because it's unusual to have a data in a column have multiple formats!

The data in a column is supposed to carry one format based on the data type.

I advise you to use two columns, one for a year, one for specific dates as you mentioned before, and to guide the users somehow to use the correct column.

[#3]

Hmm, I'm reviewing the data again. Now it looks like people just used to be lazy about getting the particular date.  The last few I'm looking at all have particular dates.  I think what I'll do is require a full date and disallow putting just a year. That should be fairly easy to do, restricting date entry to either full date format or else restricting the year date to nothing less than, say, 1908... That should avoid any 4 digit entries being recorded as a full date and also capture all of our very oldest people...

People here are used to using either Jan 1 or July 1 as the specific date of birth if it is only known up to the year.

My concern with using the general format was that, one, excel sometimes will try to format your data for you at some point, even if you don't want it formatted and also it would be difficult to do error checking / data validation.  Thanks for the replies!