Forum Discussion
Data validation date format across different computers
I have data validation limiting input to dd/mm/yy (e.g. 18/06/20). The cell displays as dddd, mmmm dd, yyyy (Thursday, June 18, 2020).
The problem is that other staff, when editing this spreadsheet, are prevented from adding 18/06/20, presumably because their system's date format is set to mm/dd/yy (06/18/20), thus making 18/06/20 an invalid entry.
While investigating this I have also found that my own system is allowing me to enter as yyyy/mm/dd.
I need consistency in the entries as I am analyzing data based on the date and can't have 5th June 2020 mixed up with 6th May 2020 or even 20th June 2005 etc..
Is data validation of use in this situation if I can't use it to control the data entry by different users? Is there something else I should be doing here? Can I use a formula to refer to the computer's system settings as the prevailing date format?
Thanks,
Diccon
3 Replies
Data validation could be applied if some formal logic is in place. I don't see it here, how do we know 5th June or 6th May is correct date?
- Thanks Sergei,
I guess that is my question. I followed instructions for restricting the date format, but it isn’t working. So I am asking what else I need to do.Afraid format won't help. If the user types 05/06/20 it could be May or June depends on format, but we may validate only value. For example, it shall be current month, i.e. June and current year, i.e. 2020 - with such logic we may validate the date.
