Data Validation when entering hours over 24

[Andy]

Hiya,

I'm having problems adding data validation to some cells where the
time can be over 24 hours.

I know that the cells must be formatted [h]:mm to be viewed properly
and have successfully added validation to allow only times of 0:00 and
above (including 24+) by using Time - Greater Than - 00:00:00.

The problem:

If a user types 1 and then hits enter it seems to convert it to days
so the cell shows 24:00.

What I need (if possible) is to force the user to add a colon to make
sure that the number of hours are entered correctly. This could be
anywhere from 00:00 to 100:00+

I have seen code that allows users to enter times without the colon(eg
entering 2300 will show as 23:00 in the cell) but this only applies to
the 24 hour clock and was unable to modify it to suit my needs.

Any help is appreciated!

 

[DCG-jaeson]

Hi,

You can use Data Validation, Then set it into TIME, this will prevent
the user to encode any type of characters but time only.

Hope it will help.
~jaeson

 

[DCG-jaeson]

Hi,

You can use Data Validation, Then set it into TIME, this will prevent
the user to encode any type of characters but time only.

Hope it will help.
~jaeson

Another, if you want the user that can encode greater than 23:59, you
can set the Data Validation into DATE.

Hehe!
~jaeson

 

[TheGlimmerMan]

Hi,

You can use Data Validation, Then set it into TIME, this will prevent
the user to encode any type of characters but time only.

Hope it will help.
~jaeson

Or make a list of valid time entries, and use the list as a drop down
list.

 

[Vacuum Sealed]

Absolutely

Setup up Data Validation Dropdown List

Make your list in increments of 15 min blocks which helps with continuity.

00:00 = (Midnight)
00:15
00:30
00:45
01:00

Down to

23:45 = 15 (minute to midnight)

etc........

This way users can only select what you want them to....

HTH
Mick

 

[WallyWallWhackr]

Absolutely

Setup up Data Validation Dropdown List

Make your list in increments of 15 min blocks which helps with continuity.

00:00 = (Midnight)
00:15
00:30
00:45
01:00

Down to

23:45 = 15 (minute to midnight)

etc........

This way users can only select what you want them to....

HTH
Mick

I go one step further with my example on the MS template site.

I allow the user to select the increment size.

1,2,5,10,15, & 30 minute increments are included.

That way, any billing or timekeeping paradigm can be used.

http://office.microsoft.com/en-us/templates/weekly-labor-expenditure-...

or

http://tinyurl.com/24rgy67

 

[Andy]

Thanks for the help but it doesn't really solve my issue.

I need to allow entries of up to 1000 hours. It wouldn't really be
suitable to have that many entries on a list as we need the times to
be accurate to the closest minute. Even if I have one list for hours
and one for minutes the lists would still be too long.

As mentioned I have tried time and date validation but my main concern
is that someone could enter 1, resulting in the cell returning 24:00
as opposed to entering 1: and getting 1:00.

I guess my main bet is to rely on the competence of users but I would
have preferred to get a solid validation alternative.


Thanks anyway.

 

[WallyWallWhackr]

Thanks for the help but it doesn't really solve my issue.

I need to allow entries of up to 1000 hours. It wouldn't really be
suitable to have that many entries on a list as we need the times to
be accurate to the closest minute. Even if I have one list for hours
and one for minutes the lists would still be too long.

As mentioned I have tried time and date validation but my main concern
is that someone could enter 1, resulting in the cell returning 24:00
as opposed to entering 1: and getting 1:00.

I guess my main bet is to rely on the competence of users but I would
have preferred to get a solid validation alternative.


Thanks anyway.

Then your list would have to be more explicit than a mere time
reference. It would have to be time *and* day.

 

[Lynz]

Hiya,

I'm having problems adding data validation to some cells where the
time can be over 24 hours.

I know that the cells must be formatted [h]:mm to be viewed properly
and have successfully added validation to allow only times of 0:00 and
above (including 24+) by using Time - Greater Than - 00:00:00.

The problem:

If a user types 1 and then hits enter it seems to convert it to days
so the cell shows 24:00.

What I need (if possible) is to force the user to add a colon to make
sure that the number of hours are entered correctly. This could be
anywhere from 00:00 to 100:00+

I have seen code that allows users to enter times without the colon(eg
entering 2300 will show as 23:00 in the cell) but this only applies to
the 24 hour clock and was unable to modify it to suit my needs.

Any help is appreciated!

Could you have them enter the time using a UF and have separate boxes
for hours and minutes? Then sort out the formatting after.