Forum Discussion
How to limit user to enter correct date format values
For VBA you need macro-enabled workbook which usually a headache in corporate environment. Office Script actually gives nothing, it doesn't work by event. With it you may only check (pressing the button or like) if dates were entered correctly, but in your case that's actually the same as conditional formatting but more complex.
In general yes, you may work online with macro-enabled workbook, the only macros won't work.
To ensure that users enter correct date format values in Excel, especially when using both the Excel app and Excel Online, you can follow these steps:
1. Data Validation (with excel for web): Apply data validation rules to restrict the entry of incorrect date formats. This will work when users manually type in values.
- Select the cells where you want to restrict date entry.
- Go to the "Data" tab.
- Click on "Data Validation" in the "Data Tools" group.
- In the Data Validation dialog box, select "Date" under the "Allow" dropdown.
- Choose the desired start and end date range.
- Select the correct date format under the "Data" dropdown.
- Optionally, provide an input message and error message.
- Click "OK."
2. Protect Cells: Protect the cells where dates need to be entered using data validation. This will prevent users from copying and pasting invalid data.
- After applying data validation, go to the "Review" tab.
- Click on "Protect Sheet" in the "Changes" group.
- Set a password if desired or leave it blank for no password.
- Ensure that "Select locked cells" is unchecked.
- Click "OK."
3. Excel Online Editing: While Excel Online doesn't support data validation as of my knowledge cutoff in September 2021, you can still set up protection for cells in the Excel app. Make sure users understand that copying and pasting may not maintain validation rules.
4. Communication and Training: Educate your users about the correct date format and the importance of entering data accurately. Provide them with clear instructions on how to manually enter dates and avoid copy-pasting.
5. SharePoint Version History: Encourage users to make use of SharePoint version history. If someone accidentally enters incorrect data, you can always revert to a previous version of the file.
6. Advanced Solutions: If your organization requires stricter control, consider using PowerApps with SharePoint lists to create customized data entry forms that enforce date formats. This can be more complex to set up but provides more control.
Remember, a combination of communication, data validation, and protecting cells can help improve data accuracy. It is important to find the right balance between usability and control based on your organization's needs. The text and the steps are the result of various AI's put together.
My answers are voluntary and without guarantee!
Hope this will help you.
"While Excel Online doesn't support data validation as of my knowledge cutoff in September 2021"
It's August 2023 and Excel for web supports Data validation.
- That's what happens when you mean things differently and pass other things on. This is what happens when you mostly work with SharePoint Server 2016. Anyway thanks again for the hint :).