Preformat cells to automatically convert date to uppercase 03JAN25, instead of 03Jan25

Question

I tried this:
To make a Data Validation that stops anytyping of lowercase letters you follow the steps below. 
1. Right-Click desired Column and select Data Validation.
2. In the Data Validation pop-up, select the + Add rule.
3. Make sure your Apply to Range is set to the correct Column/Range.
4. Click the Criteria dropdown bar and select the second to last option which is Custom formula is.
5. In the text box under that dropdown put this formula =exact(upper(K:K),K:K) inside.
6. Now check the box next to Show help text for selected cell (If you do not see it click on Advanced Options).
7. In the new text box, put something like Uppercase Only!!.
8. Under If the data Is invalid:, select Reject the input. 
9. now click done and no one will be able to put lowercase words in the range. 
- But the above did not work. I don't think it works with dates anyway, but when I typed in just one lower case letter it didn't prevent me from doing so or give a warning, and didn't convert it to upper case. I think the point of the above Data Validation, if working, is to make you type all caps in a cell in the first place.
- Ideally I would just like to be able to type in a date lowercase, i.e. 03jan25 and have it change automatically to 03JAN25.
- I don't want to use a formula that references another cell to achieve this.

--Hyde · Accepted Answer

is there a way to convert to all caps, short of changing the font to Bona Nova SC
Hi Greg,
Thank you for the explanation. The all caps font workaround will not do the trick for you.
The arguably best way to fix the issue would be to make the back-end system less braindead. If that is not possible, the "format as plain text" is probably the easiest way.
 
You can also use an onEdit(e) script to automatically convert data entered in that column into uppercase. To learn scripting, go to the Beginner's Guide, the New Apps Script Editor guide, the Fundamentals of Apps Script with Google Sheets codelab, the Extending Google Sheets page, javascript.info, Mozilla Developer Network and Clean Code JavaScript.
 
 when I type a lower case letter into a cell, it does just that, results in the lower case letter, and doesn't prevent me from using lower case
 
Did you apply the rule to the column K:K? Are you entering those text strings in that column?
 
I share a google sheet for others to input the data
 
Have you considered creating a form instead of sharing a spreadsheet for data entry? Forms have their own data validation, and it might be helpful here, although the values may again get automatically coerced to mixed case dates when they are saved in the spreadsheet?
 
Cheers --Hyde

--Hyde · Accepted Answer

don't think it works with dates anyway
 
Hi Greg,
 
The rule you quote in the question should work fine with text strings when applied to the column K:K, and it would work fine with dates as well provided that the date format is uppercase-only.
 
There is some complexity. Values like 03Jan25 are automatically coerced to dates by Google Sheets, but the exact() and upper() functions read them as text in the format shown in the spreadsheet, so the rule indeed does what you want. The problem is that a value like 03JAN25 will also be interpreted as a date and automatically converted to 03Jan25 when the spreadsheet uses the United States locale and no special date format, and it will thus fail the validation.
 
It is unclear why you need to use that specific format with dates, and also why it is important that the dates are in uppercase. If it is for looks only, you can use format the column in an "all caps" font. To get an "all caps" font, click the Font list in the toolbar and choose Bona Nova SC.
 
Cheers --Hyde