Lock Date Format

[PD]

Is it possible so a particular cell will only take date if entered in the
dd/mm/yy format?

 

[Harald Staff]

No. Cells will accept and translate and use all reasonable date formats. And
they should. Why do you want to reject entries like "june 1 09" and how
would excel know if 04/06/08 is intended to be april or june?

Best wishes Harald

 

[PD]

Hi Harald.

Thank you for the below.

One of our more financial risk reducing MIs depends on the calcualtion of
the date correctly.

The MI calcuates 120 days from the input date and triggers a message if the
date is nearing the present date.

If the date format being used is 14-Mar-01, it will help prevent a
situation( such as youve mentioned below) where the date is read as 1/4/2008
as 1 april against 4 jan, etc.

Therefore, to prevent any such errors, we need the date to be only input as
14-Mar-01 and lock the format so no other date entry format is used.

Is this possible.

 

[PD]

Alternatively, can we arrange a VB or excel macro that converts the date
enetered in cell to 14-Mar-01 format, which allows the user to check the
month that has been taken for the calculation.

 

[Harald Staff]

The interpretation of 1/4/2008 depends on the settings on the computer. It
will most likely follow the understanding of the user of the computer. If a
user means it to be april first then his computer is most likely to agree
and Excel says april it is and stores it as that date. When you open it on a
computer with a m/d/y setting, this very same entry will be displayed as
4/1/2008 and still mean the same as the entry. A date is a date,
presentation is just a custom view.

Best wishes Harald