Forum Discussion
Allow either a specific date or only the year in a single column
David,
Simply, you can format the column as https://support.office.com/en-us/article/available-number-formats-0afe8f52-97db-41f1-b972-4b46e9f1e8d2
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.
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!