is it possible to validate so that a user can only input in that format eg.37:00 ..i have tried with normal validation and it goes pear after 24 hrs..does htis have to be done in VBA if so how easy is it? im kinda new to excel

beastwood_uk,

You can certainly grab the string that the User entered and then examine it for validation purposes.

I'm a little confused, though, from your example of 37:00 -- how is it that you go past 24??

CarlR

its basically so people can input the hours they work eg from 12 hours through to 37 hours a week..but i want to fix it so they can only enter in ##:## format i can use normal validation but it does not accept anything past 24:00 :(

beastwood_uk,

I would suggest that rather than trying to validate it as time (which it is NOT since you're looking for cumulative hours worked), that you simply parse the string and examine it numerically.

Assuming that the string is coming from a textbox (such as Textbox1) I would do something like the following. [NOTE: In the code below, I say Goto ERRORMSG to indicate that this is where the validation has failed and the User needs to be made aware of that.]

' ************************************************** ******************
' ************************************************** ******************
Dim strTemp as String
Dim strHours as String
Dim strMins as String
Dim intHours as Integer
Dim intMins as Integer

strTemp = Textbox1.Text

If Len(strTemp) < 3 Then Goto ERRORMSG 'you at least need h:m

ColonPos = InStr(strTemp, ":") 'position of colon within string
If ColonPos = 0 Then Goto ERRORMSG

strHours = Left(strTemp, ColonPos - 1) ' to extract hours into var

strMins = Right(strTemp, Len(strTemp) - ColonPos) ' get the minutes

If Len(strHours) = 0 or IsNumeric(strHours) = False Then Goto ERRORMSG

If Len(strMins) = 0 or IsNumeric(strMins) = False Then Goto ERRORMSG

intHours = Val(strHours) 'place Hours into an integer var
intMins = Val(strMins) ' same for mins

If intHours > 37 then Goto ERRORMSG

If intMins > 60 then Goto ERRORMSG
' ************************************************** ******************
' ************************************************** ******************

That should pretty much take care of it.

For more info recommend http://www.vba-programmer.com

CarlR