# If function - time in - time out

#### bong25

##### New Member
Hi, there again,can you help me by providing a formula (not VBA) an "if" function probably to check the time in value is greather or lesser than say "6" then throws a result iether high or low and similar in time out if the value is greater than "24" throws a result high. The thing is the time in column and time out column are more than one say 6 time in and 6 time out. Please help and thanks in advance.

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Hi,

Are you dealing with Time hh:mm:ss or with Duration, i.e a number of hours ...?

Hi,

Are you dealing with Time hh:mm:ss or with Duration, i.e a number of hours ...?

Hi, thanks for quick reply. I am dealing with total working hours, I have the following:

IN COLUMN TOTAL HOURS
Code:
``=(IF(G786<>"",IF(H786<>"",(IF(G786>H786,(H786+1)-G786,H786-G786)),0),0))+(IF(I786<>"",IF(J786<>"",(IF(I786>J786,(J786+1)-I786,J786-I786)),0),0))+(IF(K786<>"",IF(L786<>"",(IF(K786>L786,(L786+1)-K786,L786-K786)),0),0))+(IF(M786<>"",IF(N786<>"",(IF(M786>N786,(N786+1)-M786,N786-M786)),0),0))+(IF(O786<>"",IF(P786<>"",(IF(O786>P786,(P786+1)-O786,P786-O786)),0),0))+(IF(Q786<>"",IF(R786<>"",(IF(Q786>R786,(R786+1)-Q786,R786-Q786)),0),0))+(IF(S786<>"",IF(T786<>"",(IF(S786>T786,(T786+1)-S786,T786-S786)),0),0))+(IF(U786<>"",IF(V786<>"",(IF(U786>V786,(V786+1)-U786,V786-U786)),0),0))+(IF(W786<>"",IF(X786<>"",(IF(W786>X786,(X786+1)-W786,X786-W786)),0),0))+(IF(Y786<>"",IF(Z786<>"",(IF(Y786>Z786,(Z786+1)-Y786,Z786-Y786)),0),0))+(IF(AA786<>"",IF(AB786<>"",(IF(AA786>AB786,(AB786+1)-AA786,AB786-AA786)),0),0))+(IF(AC786<>"",IF(AD786<>"",(IF(AC786>AD786,(AD786+1)-AC786,AD786-AC786)),0),0))+IF(AE786<>"",IF(AF786<>"",(IF(AE786>AF786,(AF786+1)-AE786,AF786-AE786)),0),0)+IF(AG786<>"",IF(AH786<>"",(IF(AG786>AH786,(AH786+1)-AG786,AH786-AG786)),0),0)``

and I have track columns one to throw if entered value in "time-in" is less or higher than "6", and one to throw if entered value is greater than 24 (for checking whether entered value is correct).

thanks

Hi again,

From this explanation, it looks as if you want to make sure a user correctly inputs numbers ...

If it is the case, you can use from menu Data > Data Validation to restrict what the user can input ...

HTH

Hi, thanks again. The thing is we have been assigned to re-enter the timesheet (from year 2006 to 2010) to excel and as we speak(type) entering those data is going on, we just want to add 2 columns elsewhere, one with condition that "if 'time-in' cells values is > or < than 6 throws a text shows "value is high" or "value is low", and one similarly to throw a text shows "more than 24". Thanks again

thanks, but that code is for one cell, how to format it to check several cells, my apology if i am a bit lazy or something.

Hi,

Once you have typed in both formulas, say in cells I2 and J2, you can copy these formulas all the way down ... till the very last row of your data ... formulas will adjust themselves.

HTH

Replies
1
Views
105
Replies
3
Views
253
Replies
9
Views
686
Replies
1
Views
170
Replies
21
Views
735

1,196,380
Messages
6,014,958
Members
441,857
Latest member
saraguchi2316

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back