Date & Time formatting - Change any date / time entry to standard format used by conditional formatting (VBA)

[fvdk]

For a new project I'm working on a form in Excel. One of the field that need to be filled in on the form is time and date.

For this fields I use data validation for Time and Date to make sure the users enter a time and for the other field a date. However in this valition the time needs to be entered as HH:MM (14:30) and the date as DD-MM-YYYY (24-10-2011).

One of the comments from the users was that they want any time /date format to be accepted. How to do this and still keep the validation check: i.e. change the time / date formats that are entered to the standard time format:

So change Time:
HH-MM
HHMM
HH.MM
to
HH:MM

and change Date:
DD:MM:YYYY
DDMMYYYY
DD.MM.YYYY
to
DD-MM-YYYY

Is there any function in VBA that can help with this?

Many thanks in advance

 

[fvdk]

Sorry to bump this but was hoping someone could help me!