Conditional Formatting to be applied to Cells in a Column

Hello Guys,

It the Appraisal Time again and as usual its the only time when HR is seen as working in the Office...

However, I want to customise the table in such a way that there are no other values apart from the ones that I have already chosen that get entered.
For Ex. I have already customised the Grades Column in the Excel Sheet using Data Validating Tool and a specified list from (M01 - M08). Hence if the user tries to enter any other value, it will flash an error saying... choose values from M01 to M08. Similarly a table has been worked out for the Rolls too.

I have an issue now. The Employee Number in the system is a standard five digit code. The text length has to be five characters.
For Ex. If an employee's number is 1, it has be to represented as "00001". Similarly if the employees number is 9999, it should be represented as "09999".

Now I have tried a couple of things but they do not seem to get the desired results.

My question is whether Data Validation Technique will do the job or do I have to use something else?

Could I bother you again for a similar query...

The date format, if it too has to be specified in a similar format.
Example the US follows MM / DD / YYYY, where as we follow DD / MMM / YYYY.

Now what happens is certain people who are unaware end up using either this / that format which creates confusion. Hence can a similar formatting what we achieved for the employee number be done to the entire column to achieve DD MMM YYYY. For Ex. my DOJ (Date of Joining) is 01 April 2001. So the Representation is DOJ column should show 01-Apr-2001.

Thanks.

Dibbins Sir, could you please help me out with this?

Good Morning,

Hi, I agree the date what we see in the cell is only a number. It is then changed to visually appear as a date. Either formats.

Now, you are of the opinion that I should actually format a cell as a Date.
Let me give it a go and we'll see if it works.

What I am trying to do here is to give it a range as well as format it. The format is not so relevant but the data validation (range very much is)

However, i tried to use data validation and it did not work.

I tried to do the same. In the Data Validation option in had selected the starting date as 01 April 1971 & the end date as 10 October 2014. However, it still continued to accept dates prior to 01 April 1971.

I am sure I must have fumbled up somewhere.

Anyways, let me give it a try once again.

Thanks again Sir.

Ok...

Great. Let me try that.