# IF AND statement for analysing a time range

#### dezinsektor

##### New Member
Hi, I'm trying to make a formula that will take in account two different times (hh:mm:ss) and if the value is between those times then ignore the result and write desired time called reset time.

this is my formula in cell V4 that does not work:
=IF(AND(\$G\$4<(U4+\$H\$4);(U4+\$H\$4)<\$L\$4);\$G\$4;(U4+\$H\$4))

G4 H4 L4 U4 V4
 reset time waiting time injection time start end start end start end start end start end start end start end 9:30:00 4:00:00 0:03:30 9:30:00​ 9:33:30​ 13:33:30 13:37:00 17:37:00 17:40:30 21:40:30 21:44:00 1:44:00 1:47:30 5:47:30 5:51:00 9:51:00 9:54:30

The result in V4 should be reset time in G4.

Thank you.

Cheers

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

#### shknbk2

##### Active Member
I'm not sure what your problem is. Other than changing the semicolons in your formula to commas, I seem to get the G4 value.

(By the way, your data above would have been easier to understand if you would have used the XL2BB table export feature like below)

Book1
GHIJKLMNOPQRSTUVW
3reset timewaiting timeinjection timestartendstartendstartendstartendstartendstartendstartend
49:30:004:00:000:03:309:30:009:33:3013:33:3013:37:0017:37:0017:40:3021:40:3021:44:001:44:001:47:305:47:305:51:009:30:009:54:30
Sheet1
Cell Formulas
RangeFormula
V4V4=IF(AND(\$G\$4<(U4+\$H\$4),(U4+\$H\$4)<\$L\$4),\$G\$4,(U4+\$H\$4))

#### dezinsektor

##### New Member
thank you, but semicolon is not an issue, this is regional and language setting. It is interesting that works fine on your computer.
?

#### Fluff

##### MrExcel MVP, Moderator
Check that all your values are real times & not text.

#### dezinsektor

##### New Member

Check that all your values are real times & not text.
yes, I did, everything is in time format hh:mm:ss

#### dezinsektor

##### New Member
Here is my Excel:

#### Fluff

##### MrExcel MVP, Moderator

Which of those cells do you think is wrong?

#### dezinsektor

##### New Member
Which of those cells do you think is wrong?
V4, it should be 9:30, not 9:51.

#### Fluff

##### MrExcel MVP, Moderator
Why? U4:H4 is greater than L4

#### shknbk2

##### Active Member
@dezinsektor
The problem is that you are only seeing the time of day values in the cells. A Date or Time variable is really a number with both the integer and decimal parts. The integer indicates the day and the decimal is the hour. You can see from your data below that while you are only seeing the hours of the day, the hours added together actually spill into the next day. V4 isn't just checking the hour of 9:30 against the hour of 9:51 (U + H). Instead, it is checking the Date variable as a whole.

In the table below, I copied your Time values down a row and formatted them as numbers. You see that U4 isn't just 5:47:30, but it is that time a day later. You can also see that U + H is not less than L, so your If fails.

 reset time waiting time injection time start end start end start end start end start end start end start end start end buš 1 9:30:00 4:00:00 0:03:30 9:30:00 9:33:30 13:33:30 13:37:00 17:37:00 17:40:30 21:40:30 21:44:00 1:44:00 1:47:30 5:47:30 5:51:00 9:51:00 9:54:30 13:54:30 13:58:00 0.39583 0.16667 0.00243 0.39583 0.39826 0.56493 0.56736 0.73403 0.73646 0.90313 0.90556 1.07222 1.07465 1.24132 1.24375 1.41042 1.41285 1.57951 1.58194

Try using the Evaluate Formula feature in the Formulas tab. Select V4 and run through the evaluation. You can see where the formula fails the If statement.

Replies
2
Views
202
Replies
3
Views
170
Replies
3
Views
49
Replies
12
Views
214
Replies
0
Views
110

1,148,179
Messages
5,745,199
Members
423,932
Latest member
pablo2

### 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