0

I want a column in my spreadsheet to only have dates in the yyyy-mm-dd format. The format can be set using the Format option but the problem appears when I start using data validation. Now, if I use the standard data validation of "is Date" and do a "Reject Input" in case of any other type of entry, it works perfectly ... until someone copy pastes data in this column - and as soon as that happens, data validation goes away (which is understandable since there's been a paste job done). How can I prevent this problem?

I want the sheet to reject any input or copy paste of data that's not in the yyyy-mm-dd format

Already tried data validation plus formatting

Note: multiple people with varying degrees of tech proficiency will be using this sheet and hence date validation is important

Rubén
  • 34,714
  • 9
  • 70
  • 166
Rachit Saran
  • 1
  • You could use a script to check the value of each modified cell in that column and format it after each entry. Could also use it to remove any value that isn't a date. Check out https://developers.google.com/apps-script/reference/spreadsheet/range setNumberFormat(). – CalamitousCode Dec 24 '18 at 10:45
  • Can you point me to some existing solutions? – Rachit Saran Dec 24 '18 at 11:22
  • Check out this question. https://stackoverflow.com/questions/47769823/custom-date-validation-in-google-sheets-with-popup-alert – CalamitousCode Dec 24 '18 at 11:36
  • I don't think that you can prevent copy paste as you wish. I think the only answer is a webapp that provides a similar functionality and not letting users have direct access to the spreadsheet. You also get client side validation as well. – Cooper Dec 24 '18 at 17:28

1 Answers1

0

If you are allowing users to write directly into Google Sheets cells, there isn't a way to prevent that they change the date formatting.

The alternatives are to reapply the desired format, either manually or automatically, or to use another method to get user input, like a Google Apps Script prompt or custom dialog.

Rubén
  • 34,714
  • 9
  • 70
  • 166
  • Thanks! I have been searching for a concrete answer and increasingly it's turning out that there's no one end solution. It appears the only way is to use couple of solutions in combination as described in other related articles – Rachit Saran Dec 26 '18 at 12:15