SOLVED

How to limit user to enter correct date format values

Copper Contributor

Hi All,

I have excel file saved in SharePoint location and multiple users are updating the same.

in that file we have 4-5 date columns where I found users are entering sometime incorrect date format values. 

I want to limit the users to enter the date in correct format and for that I tried to setup a validation as well. but some users are copy pasting over it and that validation failed. 

 

Can any body suggest me how to solve this type of issue ?

 

Note : some users are working in excel app and some are using excel online and excel file contains overall around 100 columns.

 

7 Replies

@Negi1984 

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.

@NikolinoDE 

"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 :).

@Negi1984 

Yes, Excel allows to paste the value on the cell ignoring data validation. Moreover, it will be removed if you paste the cell without data validation.

Workaround with VBA doesn't work in your case since doesn't work on Excel for web.

Another workaround is to use conditional formatting. If the date is entered in wrong format the value is text, not date. Dates in Excel are actually numbers. You may highlight in red texts entered instead of dates checking type of the value in the cell(s).

@SergeiBaklan, thanks a lot for the idea. I will try to apply the same logic. Also just want to reconfirm ,

if we can use VBA and office script together which work on both cases if user working in Excel app than VBA will work and in case any user working in Excel online version than office script ? 

 

any guidance if you can provide here as well.

Thanks a lot @NikolinoDE for your valuable suggestion.
best response confirmed by Negi1984 (Copper Contributor)
Solution

@Negi1984 

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. 

1 best response

Accepted Solutions
best response confirmed by Negi1984 (Copper Contributor)
Solution

@Negi1984 

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. 

View solution in original post