To get correct time format with incorrect value??

[Gamnis]

Hi

My name is Richard and I'm new to your webbsite.

I'm working on an excell sheet that is going to collect information from different people with differrent knowledge in excel.

Basically most of them does not know the right format for example time.

To get the right time format you need to wright ex 10:12

It's common for many of the users to wright either 1012 10.12 10 12 or 10,12

Right now I have solved it by making the cellformat to time hh:mm so if the user wrights 1012 the outcome is 00:00 so it forces them to wright it right. Witch makes many of them quite angry...

So my question is if there is some kind of VB code or format that automaticly rewrights the format to hh:mm


Thankfull for any sugestion


Best regards

--

Richard

(I'm from sweden so sorry for any bad english or if I translate anything wrong from my excel copy)

 

[Radu]

Hi Richard, and welcome!


I've had the same problem some time ago, and in my case I've managed to solve it by adding a new sheet called "Instructions", in which I've explained to people exactly what I wanted them to do. Maybe this could work for you too...

In any case, you could use in another worksheet or range (maybe in a hidden column right next to the one in which the users will input data) the following formula: =LEFT(A3;2)&":"&RIGHT(A3;2) (-- in my case, I've put the data in cell A3)

It works with all of the examples that you have given. Then, you could use the results in order to do eventual calculations / aggregating from there. Of course, you would have to format the cells which will contain the result as Custom - hh:mm type, so those eventual calculations could be performed.

Let me know if this is helpful.


Best regards,

Radu

 

[Gamnis]

This worked out just fine :D

Thx alot!

And have a nice day Radu