• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel related doubt

J

Jagdev Singh

Active Member
Hi Experts
I have a column A where different users enter respective date (DD/MM/YYYY). Is there any way in excel to restrict user to add date in any other format in this column.
Can we do something with CF. The color of the cell will change if anyone try to add the date in different format.
Regards,
JD
 
Just curious, but why does it matter how user inputs the data? Once the data is entered, it will be displayed in whatever format the cell is formatted to display. E.g., if cell is set to display as dd-mmm-yyyy, and I input 1/21/15, XL will accept it, but display it as 21-Jane-2015.

Additionally, it doesn't affect any calculations XL does. Not saying it can't be done, but it may be a lot of work for no real gain.
 
Hi Luke thanks for the clarification. I have applied CF on this column and most of the user adds the date in either mm/dd/yyyy or dd.mm.yyyy or mm.dd.yyyy format. The logic of CF gets totally change with such dates. That's the reason I was asking for any CF which will highlight such dates in somecolor to make me aware that the dates are not in proper format.

Regards,
JD
 
Hi,

If the user will input in mm/dd/yyyy & date will <13 then CF would not work as excel will auto convert it to dd/mm/yyyy.
 

Attachments

  • JD.xlsx
    9 KB · Views: 0
Hi Deepak and Luke

My current CF is - =$A2>DATE(2015,4,17)+11

It will highlight the date after 11 days from 17th April, 2015.

Regards,
JD
 
Hmm. If that CF is failing, then it's not the format so much of how user is inputting data, it's that XL is mixing up the month day. Sorry to keep asking questions, but could you show an example workbook, with both good/bad data in it?
 
Hi Deepak

Your CF cover the date fromat dd.mm.yyyy. It is not working in some dates.
There are few date in dd/mm/yyyy (10/04/2015) and mm/dd/yyyy (04/10/2015). I think here excel might get confused what to consider am I right?.
 
The issue is how XL is interpretting the data. I'd suggest formatting the cells to display as:
dd-mmm-yyyy

which removed any ambiguity. This would give instant feedback to users that they've entered a date wrong. Then, just add a message showing what type of format XL is expecting. See attached for example
 

Attachments

  • Date Example.xlsx
    8.4 KB · Views: 0
IS this a data dump from somewhere else, or a list of dates that should all be processed as mm/dd/yyyy, or ...? There was no explanation in the workbook.
 
Hi ,

I think there is a lot of discussion on what is right and wrong ; I think the discussion should focus on what you want.

What are the formats that you want the dates to be entered in ? Which are the correct formats ? Can we assume that they are :

mm/dd/yyyy or dd.mm.yyyy or mm.dd.yyyy

If so , this is absolutely wrong , since as you yourself have mentioned , a date such as October 4 , 2015 may be entered by one user as 04.10.2015 , and by other users as 10.04.2015 or 10/04/2015.

A similar mixup can happen with April 10, 2015 or any other date where both the day and month components are less than 13.

The issue here is not of CF or anything to do with Excel ; the issue is to decide what the user had in mind when they entered data.

You need to put in some restrictions in data entry so that these possibilities can be ruled out , after which we can discuss Excel related issues.

Narayan
 
Hi All

Thanks for your feedback

@ Narayan - The Agreed format to enter the date is dd/mm/yyyy, but few user mistakenly add the date in different format.

@ Luke - I dump the sample dates from the raw file for your reference.

Regards,
JD
 
Hi ,

If the agreed format is dd/mm/yyyy , then the only things that can be checked are :

1. Check that the number of slashes / is 2.

2. Check that the slashes are in positions 3 and 6.

3. Check that the value of the digits in the 4th and 5th positions is less than 13.

4. Check that the value of the digits in the 1st and 2nd positions is less than 32.

5. Check that the value of the entered data falls between 2 valid dates.

If all these checks are passed , then it is a valid date. If you think these are insufficient , feel free to add more checks.

Doing all of the above using formulae is cumbersome ; doing it using a UDF is easy.

Narayan
 
Hi Narayan

Thanks for sharing the above information. I have instructed my colleague to follow standard format. I believe this will solve my query.

Regards,
JD
 
You must log in or register to reply here.
Back
Top