# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  >  Forcing date format (dd/mm/yyyy) using data validation

## reddwarf

Hi,
First post, great site and glad I found it - a great resource of tips and tricks!

I have a question.  I need users to enter dates into a spreadsheet (dd/mm/yyyy) but I need the spreadsheet to trap the error should an incorret date be entered,  I know this can be done via data validation and believe me when I say I have searched this site and googled Google but I cannot find an exact answer.

Can someone help?

Thanks

----------


## DonkeyOte

When applying the validation use the Date option and apply Start & End dates accordingly.

_Format the cell to use dd/mm/yyyy format - once valid date is entered it will be formatted as required regardless of how it was input (eg 1/2/9 will display correctly as 01/02/2009)_

----------


## reddwarf

Thanks DonkeyOte,
Worked a treat - I never knew it was that simple!

----------


## arthurbr

If you are satisfied with the solution(s) provided, please mark your thread as Solved. 

*How to mark a thread Solved*
Go to the first post 
Click edit
Click Go Advanced
Just below the word *Title* you will see a dropdown with the word No prefix.
Change to Solved
Click Save

----------


## Masud8956

> When applying the validation use the Date option and apply Start & End dates accordingly.
> 
> _Format the cell to use dd/mm/yyyy format - once valid date is entered it will be formatted as required regardless of how it was input (eg 1/2/9 will display correctly as 01/02/2009)_



Nice tip!

I am facing problem while entering a date of a previous year. e.g. it is the year 2018 now. I have a data sheet where I require 01 Jan 2008, 2009, 2010 and so on. Once I try excel notifies me it to be invalid input.

How can I insert previous years while having validating rule as you mentioned above?

----------


## AliGW

Unfortunately _your post does not comply with Rule 2 of our Forum_ RULES. *Do not post a question in the thread of another member -- start your own thread.* 

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread. 

Old threads are often only monitored by the original participants.  New threads not only open you up to all possible participants again, they typically get faster response, too.

----------

