If function - time in - time out

[bong25]

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.

 

[James006]

Hi,

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

 

[bong25]

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

=(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

 

[James006]

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

 

[bong25]

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

 

[James006]

May be

=IF(G2<6,"Value is Low","Value is High")

and

=IF(H2<24,"Value is below 24","Value is higher than 24")

HTH

 

[bong25]

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.

 

[James006]

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