Time Validation

[Passman]

I am trying to validate times entered into a range of cells. Most of the users are not used to using excel and will enter time as 15.30 and not as 15:30 format. I can't use data validation as it does not recognise the hh.mm format.

Is their a way to use a vba routine to achieve inputs of 15.30 and 3.30pm to show as 15:30 in the inputted cell.

Thanks in advance

 

[VoG]

Try Data Validation > Allow > Time. Set the low value to 0:00 and the high value to 23:59.

 

[Passman]

Thanks for your fast reply but the problem I have with your solution is that it prompts the user for the correct format.

I was hoping for a solution that would automatically make the adjustment without another user input.

If it's not possible then I will have to accept this option but I would rather not!

 

[VoG]

You can set the Input Message to "You must enter in the format hh:mm" or similar.

 

[Passman]

I guess this will have to be the solution - thanks for your help