Time data validation and total time

[Tony James]

Hi

I'm using data validation on cells A1:B7 to restrict entry to time
values. Validation criteria is set to the following:

Allow: Time
Data: between
Start time: 00:00:00
End time: 23:00:00

This is all working well.

Now I would like to use data validation to limit entry to time values
and also check that the sum of the cells in A1:B7 plus a base time in
G1 is not greater than 36 hours. That is, if cell G1 has a time value
of 20:00 then whenever a time entry is made in cells A1:B7 and if
Sum(A1:B7,G1)>36:00 an error message is displayed and entry disallowed
in the usual way.

Can this be done with worksheet functions or will I need to use VBA?

Many thanks - Tony

 

[Tony James]

Hi

I'm using data validation on cells A1:B7 to restrict entry to time
values. Validation criteria is set to the following:

Allow: Time
Data: between
Start time: 00:00:00
End time: 23:00:00

This is all working well.

Now I would like to use data validation to limit entry to time values
and also check that the sum of the cells in A1:B7 plus a base time in
G1 is not greater than 36 hours. That is, if cell G1 has a time value
of 20:00 then whenever a time entry is made in cells A1:B7 and if
Sum(A1:B7,G1)>36:00 an error message is displayed and entry disallowed
in the usual way.

Can this be done with worksheet functions or will I need to use VBA?

Many thanks - Tony

Ok, call off the search, just found out how to do it:

Set validation criteria to:
Allow: Custom
Formula: =AND(A1>=TIME(0,0,0), A1<=TIME(23,59,59),
SUM($A$1:$B$7,$G$1)*24<=36)