Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff.
|
Lista Forumurilor Pe Tematici
|
Mrrrr's Forum (VIEW ONLY) | Reguli | Inregistrare | Login
POZE MRRRR'S FORUM (VIEW ONLY)
Nu sunteti logat.
|
Nou pe simpatie:
Cosminamoraru
 | Femeie
25 ani
Bacau
cauta Barbat
30 - 80 ani |
|
TonyTzu
Moderator
 Inregistrat: acum 12 ani
Postari: 252
|
|
When completing dates in a columns, even if you set the correct cell formatting to show dates in, for example, this format:
21.06.2018, people may mistake and write 21,06,2018 which messes the filtering by date option and more functions if you have calculation formulas that involve dates in that column.
To prevent entering wrong dates in that column, and by wrong I mean:
- 21,06,2018 - with commas instead of points (it happens as per your regional settings by using the num lock keys, or even if no num lock keys available on your keyboard, you could still mistakenly click on the comma key, it being close to the point key)
- 21.06.21018 - because you mistakenly type a wrong number for day, month, year
- maybe more, can't think of right now
you must use data validation. It's not what you think, you won't have to select dates from a list, you will still manually type them, but you will not be allowed to insert wrong characters in dates or wrong dates altogether (which are actually not dates at all, because year 21018 is too far).
1. Select the entire column / row you want to format
2. Go to the DATA tab and choose Data Validation
3. In the Settings tab, under Allow select Date
4. For start date and end date choose appropriate days depending on your data, eg:
- start date: 01.01.1990 and end date: 31.12.2099
or
- start date: 01.01.2018 and end date: 31.12.2018
5. In the Error Alert tab, Style is selected by default to Stop which is OK, under Title you can write: WRONG DATE
6. Under Error message you can write something like: ATTENTION! YOU HAVE ENTERED A WRONG DATE. DATE FORMAT SHOULD BE: DD.MM.YYYY, eg. 21.06.2018.
7. Click OK and test by entering some wrong dates (with commas, or outside your selected range of dates)
Note: In the error message you can also let people / yourself know about the allowed interval.
|
|
| pus acum 5 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 748
|
|
The only problem that this doesn't fix is mistakenly inserting the year, eg 2018 instead of 2019, which may cause problems.
You can update the start-end date fields and let people insert dates only within the current year to fix this.
For inserting the wrong day or month however, eg. 27.02 instead of 27.03, there is no solution.
Well, you could adapt them for the current month like you did for the current year, but that will take more editing on your side, every month, and it might not be productive if you have many files to do it for.
Also, by the end of this month, people might need to add due dates for next month but also from this month. You could set a 2 month time frame, eg. start date 01.03.2019 end date 30.04.2019, but as I said above, you will have to do lots of editing every month.
So there is still room for human error.
|
|
| pus acum 5 ani |
|