Worksheet_Calculate() - Problem

[Sirishgreen]

i have 2 time cells that get added together to calculate hours worked during a shift before first break. I want to say that 10 hours is the maximum hours that can be worked in any 1 shift, trying to enter a shift longer than 10 hours will call the message "sorry to many hours worked in 1 shift".

so cell b3 which is calculated from a formula contains hours worked in shift. I want the maximum value from this calculation to be 10. If the result of the formula is more than 10 then a message box appears.

Any help would be appreciated

cheers

Simon Green

[Bjornar]

If your input cells are B1 and B2 and these are summed in B3:

Select B1 and B2
Use Data -> Validation -> Custom
Insert this formula in the validation criteria:
=$B$3<=10

This maked it impossible to enter a value in B1 or B2 that makes the sum be over 10.

In the Data Validation menu you can also enter a message to the user like the one you wanted to use.

[Sirishgreen]

thanks for that i tired it out but i cant get the result i want that way.

I have some vb running to format cells b1 + b2 this doesnt work if i use validation on them cells.

I then tried to validate the cell b3 but you cant use validation on a cell that is calculated.

So I am still stuck

cheers,
Simon Green

[Bjornar]

Are you sure you have to use VBA to to format these cells ? You have conditional formatting and custom number formatting. There are often ways to avoid using VBA. What kind of formatting do you do that requires VBA to be run on a input cell ?

[Sirishgreen]

The cells are times, as excel only accepts times with a ":" I am using vb to convert a user input of 1400 to the time 14:00. I also preform calculations on the cell so they have to be in time format, looking like a time isnt good enough.

I dont know any other way of achieving this result without vb.

cheers,
Simon Green

[Bjornar]

Here are some suggestions:

1.
Use Data Validation to force the user to input times using ':' . You can then put formulas in the start time/end time fields in the validation window to make your limits of the timeinput you want.

2.
Let user input the time in the hhmm format without the ':' Don't format the cell as time just format it as number with custom number format 0000. This will not actually be a time you can use for calculation, but with formula you can convert it to a timevalue in another (hidden) cell, and use this for the calculation.

Both these avoid the VBA solution and you can use datavalidation.

It is possible to display a time in the ttmm format instead of tt:mm with custom number formatting but the user still have to use tt:mm for the input for excel to understand that it is a time. This will not help you with your input cells. I tought i just would mention it, because you can use this format for the calculated times to make them appear in the same format as your input cells if you use alternative 2 above.

[Sirishgreen]

thanks dude That works a treat I can now use validation on the cells and the times are calculated somewhere else.

Thank you for your time,

Simon Green

[Sirishgreen]

If your input cells are B1 and B2 and these are summed in B3:

Select B1 and B2
Use Data -> Validation -> Custom
Insert this formula in the validation criteria:
=$B$3<=10

This maked it impossible to enter a value in B1 or B2 that makes the sum be over 10.

In the Data Validation menu you can also enter a message to the user like the one you wanted to use.

I have now used this to limit the time on a shift

Thanks again

[marcospaterson]

if you want to display the hours like "00:00" you can use the function concatenate.
If the users are inputing data in cell B1 you could insert this formula at C1 like this "=concatenate(left(b1;2);":";right(b1;2))"
this would "work" to format the cell.