I have a shared w/sheet that requires inputters to make a time entry of time
spent on a task.
Times can range from a matter of minutes to a couple of hours.

I'm Ok with formatting cells to time but what I want to do is ensure that
they correctly enter the time as say, 1:20 for 1 hour 20 mins which gives
01:20:00. Using data validation, I can ensure they do not input as 1.2 but
when the time is less than an hour, say 25 mins and it is wrongly entered .25
or 0.25 it does not prompt the inputter re incorrect input (0:25) and the
entry becomes 06:00:00.

Assistance would be much appreciated.

--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.

Check out Chip Pearson's site he

http://www.cpearson.com/excel/DateTimeEntry.htm

He gives a method for quick data entry where you do not have to input
the delimiters.

Hope this helps.

Pete


On Mar 11, 11:04*am, Russell Dawson
wrote:
I have a shared w/sheet that requires inputters to make a time entry of time
spent on a task.
Times can range from a matter of minutes to a couple of hours.

I'm Ok with formatting cells to time but what I want to do is ensure that
they correctly enter the time as say, 1:20 for 1 hour 20 mins which gives
01:20:00. *Using data validation, I can ensure they do not input as 1.2 but
when the time is less than an hour, say 25 mins and it is wrongly entered .25
or 0.25 it does not prompt the inputter re incorrect input (0:25) and the
entry becomes 06:00:00.

Assistance would be much appreciated.

--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.

How about having the users use a six digit representation of HH:MM:SS, for
example
Turning the cell A1's format to TEXT,
Pressing Alt+D
Pressing L
In data validation criteria dropdown, selecting CUSTOM
In formula inserting:
=AND(VALUE(LEFT(A1,2))=0,VALUE(LEFT(A1,2))<=24,VA LUE(MID(A1,3,2))=0,VALUE(MID(A1,3,2))<=60,VALUE(R IGHT(A1,2))<=60,VALUE(RIGHT(A1,2))0,LEN(A1)=6)

In another cell using the following formula to convert the same to correct
time format:
=TIME(VALUE(LEFT(A1,2)),VALUE(MID(A1,3,2)),VALUE(R IGHT(A1,2)))

--
Thanx & Best Regards,

Faraz!


"Russell Dawson" wrote:

I have a shared w/sheet that requires inputters to make a time entry of time
spent on a task.
Times can range from a matter of minutes to a couple of hours.

I'm Ok with formatting cells to time but what I want to do is ensure that
they correctly enter the time as say, 1:20 for 1 hour 20 mins which gives
01:20:00. Using data validation, I can ensure they do not input as 1.2 but
when the time is less than an hour, say 25 mins and it is wrongly entered .25
or 0.25 it does not prompt the inputter re incorrect input (0:25) and the
entry becomes 06:00:00.

Assistance would be much appreciated.

--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.

Thanks very much Pete. Another thing learnt today. "Event procedures" Once
I'd de-formatted everything it worked a treat.

Cheers

--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.


"Pete_UK" wrote:

Check out Chip Pearson's site he

http://www.cpearson.com/excel/DateTimeEntry.htm

He gives a method for quick data entry where you do not have to input
the delimiters.

Hope this helps.

Pete


On Mar 11, 11:04 am, Russell Dawson
wrote:
I have a shared w/sheet that requires inputters to make a time entry of time
spent on a task.
Times can range from a matter of minutes to a couple of hours.

I'm Ok with formatting cells to time but what I want to do is ensure that
they correctly enter the time as say, 1:20 for 1 hour 20 mins which gives
01:20:00. Using data validation, I can ensure they do not input as 1.2 but
when the time is less than an hour, say 25 mins and it is wrongly entered .25
or 0.25 it does not prompt the inputter re incorrect input (0:25) and the
entry becomes 06:00:00.

Assistance would be much appreciated.

--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.

.

Glad to hear it, Russell - thanks for feeding back.

Pete

On Mar 11, 1:07*pm, Russell Dawson
wrote:
Thanks very much Pete. *Another thing learnt today. "Event procedures" Once
I'd de-formatted everything it worked a treat. *

Cheers

--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.



"Pete_UK" wrote:
Check out Chip Pearson's site he

http://www.cpearson.com/excel/DateTimeEntry.htm

He gives a method for quick data entry where you do not have to input
the delimiters.

Hope this helps.

Pete

On Mar 11, 11:04 am, Russell Dawson
wrote:
I have a shared w/sheet that requires inputters to make a time entry of time
spent on a task.
Times can range from a matter of minutes to a couple of hours.

I'm Ok with formatting cells to time but what I want to do is ensure that
they correctly enter the time as say, 1:20 for 1 hour 20 mins which gives
01:20:00. *Using data validation, I can ensure they do not input as 1.2 but
when the time is less than an hour, say 25 mins and it is wrongly entered .25
or 0.25 it does not prompt the inputter re incorrect input (0:25) and the
entry becomes 06:00:00.

Assistance would be much appreciated.

--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.

.- Hide quoted text -

- Show quoted text -