Date format.

[Heera]

Hi All

Here is my problem.

I have a tracker in which some users update dates in a column. Column
A has the user names and Column B has the dates updated by the users.
I have updated the actual dates in Column C

Users Date Updated Actual Dates
User1 3/9/10 Actual date is 03-Sep-10
User2 03/09/10 Actual date is 03-Sep-10
User3 03/09/2010 Actual date is 03-Sep-10
User4 9/3/10 Actual date is 03-Sep-10
User5 09/03/10 Actual date is 03-Sep-10
User6 09/03/2010 Actual date is 03-Sep-10
User7 09.03.10 Actual date is 03-Sep-10
User8 03-09-10 Actual date is 03-Sep-10
User9 09-03-10 Actual date is 03-Sep-10
User10 03-Sep-2010 Actual date is 03-Sep-10
User11 03-09-2010 Actual date is 03-Sep-10


I want to make sure that the date is update in dd-mmm-yy format
only.
I used the below codes but I am not getting the desired results it is
converting months into days and days into month.

ActiveCell.Offset(0, 4).NumberFormat = "[$-409]dd-mmm-yy;@"
ActiveCell.Offset(0, 4).Value = Format(ActiveCell.Offset(0, 4).Value,
"dd-mmm-yy")

I want to be sure that the date updated in the cell is getting
displayed is in dd-mmm-yy format. I want the macro to make sure the
date is updated in the dd-mmm-yy format only.
It will be greate if an message pop-ups if the format is incorrect.

Please help........................

Regards
Heera Chavan

 

[Ron Rosenfeld]

Hi All

Here is my problem.

I have a tracker in which some users update dates in a column. Column
A has the user names and Column B has the dates updated by the users.
I have updated the actual dates in Column C

Users Date Updated Actual Dates
User1 3/9/10 Actual date is 03-Sep-10
User2 03/09/10 Actual date is 03-Sep-10
User3 03/09/2010 Actual date is 03-Sep-10
User4 9/3/10 Actual date is 03-Sep-10
User5 09/03/10 Actual date is 03-Sep-10
User6 09/03/2010 Actual date is 03-Sep-10
User7 09.03.10 Actual date is 03-Sep-10
User8 03-09-10 Actual date is 03-Sep-10
User9 09-03-10 Actual date is 03-Sep-10
User10 03-Sep-2010 Actual date is 03-Sep-10
User11 03-09-2010 Actual date is 03-Sep-10


I want to make sure that the date is update in dd-mmm-yy format
only.
I used the below codes but I am not getting the desired results it is
converting months into days and days into month.

ActiveCell.Offset(0, 4).NumberFormat = "[$-409]dd-mmm-yy;@"
ActiveCell.Offset(0, 4).Value = Format(ActiveCell.Offset(0, 4).Value,
"dd-mmm-yy")

I want to be sure that the date updated in the cell is getting
displayed is in dd-mmm-yy format. I want the macro to make sure the
date is updated in the dd-mmm-yy format only.
It will be greate if an message pop-ups if the format is incorrect.

Please help........................

Regards
Heera Chavan

Conceptually, I think if you want to be absolutely sure that your
users will enter dates in a DMY format, you will have to have them
enter the date and month and year separately.

There is no way that Excel can tell, for example, if the user enters
9/3/10 if he means 3 Sep 2010 or 9 Mar 2010.

The entry is ambiguous and Excel will interpret it according to the
Windows Regional Settings.

An alternative to requiring separate entry of day, month, year would
be to display the interpreted date for user evaluation prior to
storing it. In my opinion, this would be less "bullet-proof".

 

[Heera]

Thank you Ron.

Really appriciate your comment.